Adding days and setting time

23 Jun 2006 3:59 PM

simon_s_li

Hi,

Can someone please help me with a SQL Server 2005 issue with date and
time.

I want to take the current date and time and add 2 days to it, but the
time must be set to 5pm.

If the current date/time is past 5pm it will go to the next day at 5pm.

So if the date was:

2006-06-23 15:55:46.337 then the new date should say 2006-06-25
17:00:46.337

If the current time was past 5pm then it should be as follows:

2006-06-23 19:55:46.337 then the new date should say 2006-06-26
17:00:46.337

Notice the day is an extra day because 5pm has already gone by hence it
has to go to the next 5pm, which is the next day.

Thanks.
Simon

23 Jun 2006 6:37 PM

Vern Rabe

Try this:

SELECT DATEADD(DAY, (CASE
    WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 17 THEN 2
    ELSE 3
    END), DATEADD(HOUR, 17, CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS
DATETIME)));

HTH
Vern Rabe

Show quote

"simon_s***@hotmail.com" wrote:

> Hi,
>
> Can someone please help me with a SQL Server 2005 issue with date and
> time.
>
> I want to take the current date and time and add 2 days to it, but the
> time must be set to 5pm.
>
> If the current date/time is past 5pm it will go to the next day at 5pm.
>
> So if the date was:
>
> 2006-06-23 15:55:46.337 then the new date should say 2006-06-25
> 17:00:46.337
>
> If the current time was past 5pm then it should be as follows:
>
> 2006-06-23 19:55:46.337 then the new date should say 2006-06-26
> 17:00:46.337
>
> Notice the day is an extra day because 5pm has already gone by hence it
> has to go to the next 5pm, which is the next day.
>
> Thanks.
> Simon
>
>

23 Jun 2006 6:45 PM

Lubdha Khandelwal

Something like:

DECLARE @Date1 DATETIME;
SELECT @Date1 = GETDATE();
PRINT @Date1;
SET @Date1 = CASE WHEN DATEPART(HH, @date1) > 17 THEN DATEADD(DAY, 1,
@Date1) ELSE @Date1 END;
SET @Date1 = DATEADD(HH, (17-DATEPART(HH, @Date1)), @Date1);
PRINT @Date1;

23 Jun 2006 7:28 PM

Vern Rabe

I just realized that you apparently want to retain the current seconds and
milliseconds, to be added to 5:00 PM. Seems strange, but to do that, this
should work:

SELECT DATEADD(DAY, (CASE
    WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 17 THEN 2
    ELSE 3
    END), DATEADD(HOUR, 17, CAST(CONVERT(varchar(10), CURRENT_TIMESTAMP, 110) +
' 00:00' + RIGHT(CONVERT(varchar(24), CURRENT_TIMESTAMP, 13), 7) AS
DATETIME)));

HTH
Vern Rabe

Show quote

"simon_s***@hotmail.com" wrote:

> Hi,
>
> Can someone please help me with a SQL Server 2005 issue with date and
> time.
>
> I want to take the current date and time and add 2 days to it, but the
> time must be set to 5pm.
>
> If the current date/time is past 5pm it will go to the next day at 5pm.
>
> So if the date was:
>
> 2006-06-23 15:55:46.337 then the new date should say 2006-06-25
> 17:00:46.337
>
> If the current time was past 5pm then it should be as follows:
>
> 2006-06-23 19:55:46.337 then the new date should say 2006-06-26
> 17:00:46.337
>
> Notice the day is an extra day because 5pm has already gone by hence it
> has to go to the next 5pm, which is the next day.
>
> Thanks.
> Simon
>
>