Posted by Russ Rose on 01/04/07 01:18

"Grey" <null@null.null> wrote in message
news:enhe3f$ura$1@news.dialog.net.pl...
>I have following problem:
>
> table includes times for startup and end of operation as datetime field
> related to daily shift operations:
>
> dateid date starttime endtime
>
> 458 2006-12-29 22:00 23:15
> 458 2006-12-29 00:15 01:30
> 459 2006-12-30 20:00 21:10
> 459 2006-12-30 22:15 23:35
> 459 2006-12-30 23:30 00:40
> 459 2006-12-30 01:50 02:30
>
> records are inserted for a date related to begining of the shift, although
> some operations are performed also past the midnight (actualy next day,
> ex: 2006-12-31), but belongs to same shift (group)
>
> Now I need to build a function that corrects (updates) the date of every
> operation recorded after midnight to a date+1 value, so all records
> related to same groups (458, 459, etc) that starts after midnight has
> correct date.
>
> The procedure has to update already exiting table.

Assuming no operation will exceed 24 hours it might look something like
this:

SELECT dateid,
date as startdate,
starttime,
CASE
WHEN endtime > starttime
THEN convert(datetime,date)
ELSE convert(datetime,date)+ 1
END AS EndDate,
endtime
FROM Table1



>
> Any solution?
> Grey
>
>

[Back to original message]