Adding days and setting time

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.
SimonTry 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
"simon_s_li@.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
>|||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;|||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
"simon_s_li@.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
>