Posted by Roy Harvey on 10/10/08 11:54

Here is what I think you are asking for. Start with a date, add 48
hours, and then if the result is later than 5pm, set the time to 5pm.

create table Dates (d datetime)

INSERT Dates values ('2006-08-02 08:45')
INSERT Dates values ('2006-08-02 18:45')
INSERT Dates values ('2006-08-02 22:45')

SELECT d,
CASE WHEN datepart(hour,d) >= 17
THEN dateadd(hour,17,dateadd(day,2 + datediff(day,0,d),0))
ELSE dateadd(day,2,d)
END as Future
FROM Dates

d Future
------------------------ -----------------------
2006-08-02 08:45:00.000 2006-08-04 08:45:00.000
2006-08-02 18:45:00.000 2006-08-04 17:00:00.000
2006-08-02 22:45:00.000 2006-08-04 17:00:00.000

Roy Harvey
Beacon Falls, CT

On 2 Aug 2006 03:21:35 -0700, simon_s_li@hotmail.com wrote:

>Hi,
>
>I need to add 48 hours to a specific date (datetime data type) then
>check if the time is past 5pm.
>
>So for example the time is 7pm.
>
>If this is the case I need to set the time to 5pm the next where the
>final date and time will be set to a new datetime variable.
>
>Can anyone help??
>
>Examples would be great.
>
>Regards
>Simon

[Back to original message]