Posted by Roy Harvey on 01/04/07 19:16

Something like this might do what you want.

First the simple way, with two seperate UPDATEs, one for each column:

UPDATE Operations
SET starttime = DATEADD(day,1,starttime)
WHERE datepart(hour,starttime) < 12

UPDATE Operations
SET endtime = DATEADD(day,1,endtime)
WHERE datepart(hour,endtime) < 12

Note that I chose hour 12 as an arbitrary point in the day such that
times before that are "after midnight" and times after that are
"before midnight". Choose whatever time you prefer.

Also note that this "fixes" (or destroys, you tell me) the second date
in the sample row:

>>>459 2006-12-30 23:30 00:40

This was not designated as a problem row, but I thought the second
date would be. Please clarify if this second date should not be
changed.

Anyway, the two-UPDATE approach is innefficient, as most of the
changes happen to both columns on the same row. So we combine them,
but it becomes rather more complicated.

UPDATE Operations
SET starttime = CASE WHEN datepart(hour,starttime) < 12
THEN DATEADD(day,1,starttime)
ELSE starttime
END,
endtime = CASE WHEN datepart(hour,endtime) < 12
THEN DATEADD(day,1,endtime)
ELSE starttime
END
WHERE datepart(hour,starttime) < 12
OR datepart(hour,endtime) < 12

Hope that helps.

Roy Harvey
Beacon Falls, CT

On Thu, 4 Jan 2007 16:17:13 +0100, "Grey" <null@null.null> wrote:

>
>>>dateid date starttime endtime
>>>
>>>458 2006-12-29 22:00 23:15
>>>458 2006-12-29 00:15 01:30 --Problem?
>
>>>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 --Problem?
>>>459 2006-12-30 03:30 04:30 --Problem?
>
>The start time is relevant - marked records should have a date + 1
>
>Grey
>

[Back to original message]