Set time for UTC

[simon_s_li@hotmail.com]

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

[Dan Guzman]

Re: Set time for UTC

I need to add 48 hours to a specific date (datetime data type) then

check if the time is past 5pm.

I don't fully understand your requirements and how this problem relates to
your subject but the example below should get you started.

DECLARE
@datetime1 datetime,
@datetime2 datetime

SET @datetime1= '20060802 19:00:00'

--compare only time portion
IF CAST(CONVERT(ch ar(8), @datetime1, 114) AS datetime) '17:00:00'
BEGIN
--after 5pm: make the time 5pm and add 2 days
SET @datetime2 = CAST(CONVERT(ch ar(8), @datetime1, 112) + ' 17:00:00' AS
datetime) + 2
END
ELSE
BEGIN
--5pm or earlier: leave time unchanged add 2 days
SET @datetime2 = @datetime1 + 2
END
SELECT @datetime1, @datetime2

--
Hope this helps.

Dan Guzman
SQL Server MVP

<simon_s_li@hot mail.comwrote in message
news:1154514095 .177638.37030@b 28g2000cwb.goog legroups.com...

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
>

[Roy Harvey]

Re: Set time for UTC

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@hotm ail.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

[Ed Murphy]

Re: Set time for UTC

On Wed, 02 Aug 2006 12:38:39 GMT, "Dan Guzman"
<guzmanda@nospa m-online.sbcgloba l.netwrote:

>I need to add 48 hours to a specific date (datetime data type) then
>check if the time is past 5pm.

>
>I don't fully understand your requirements and how this problem relates to
>your subject but the example below should get you started.
>
>DECLARE
@datetime1 datetime,
@datetime2 datetime
>
>SET @datetime1= '20060802 19:00:00'
>
>--compare only time portion
>IF CAST(CONVERT(ch ar(8), @datetime1, 114) AS datetime) '17:00:00'
>BEGIN
--after 5pm: make the time 5pm and add 2 days
SET @datetime2 = CAST(CONVERT(ch ar(8), @datetime1, 112) + ' 17:00:00' AS
>datetime) + 2
>END
>ELSE
>BEGIN
--5pm or earlier: leave time unchanged add 2 days
SET @datetime2 = @datetime1 + 2
>END
>SELECT @datetime1, @datetime2

Eww, magic numbers! And isn't Roy's solution using DatePart() and
DateAdd() a lot cleaner than converting to and from char?

[Dan Guzman]

Re: Set time for UTC

Eww, magic numbers! And isn't Roy's solution using DatePart() and

DateAdd() a lot cleaner than converting to and from char?

I generally prefer CONVERT rather than DATEPART/DATEADD to isolate the time
because it makes it easier to handle more complex time expressions.
DATEPART starts to get ugly when minutes and seconds need to be considered
(e.g. '17:30:45').

Whether or not one technique is cleaner than the other is fairly subjective
but the DATEPART/DATEADD method Roy suggested probably will perform better
than CONVERT.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ed Murphy" <emurphy42@soca l.rr.comwrote in message
news:ufj1d2l11f d7e3kib256ulgho h34huo2et@4ax.c om...

On Wed, 02 Aug 2006 12:38:39 GMT, "Dan Guzman"
<guzmanda@nospa m-online.sbcgloba l.netwrote:
>

>>I need to add 48 hours to a specific date (datetime data type) then
>>check if the time is past 5pm.

>>
>>I don't fully understand your requirements and how this problem relates to
>>your subject but the example below should get you started.
>>
>>DECLARE
> @datetime1 datetime,
> @datetime2 datetime
>>
>>SET @datetime1= '20060802 19:00:00'
>>
>>--compare only time portion
>>IF CAST(CONVERT(ch ar(8), @datetime1, 114) AS datetime) '17:00:00'
>>BEGIN
> --after 5pm: make the time 5pm and add 2 days
> SET @datetime2 = CAST(CONVERT(ch ar(8), @datetime1, 112) + ' 17:00:00'
>AS
>>datetime) + 2
>>END
>>ELSE
>>BEGIN
> --5pm or earlier: leave time unchanged add 2 days
> SET @datetime2 = @datetime1 + 2
>>END
>>SELECT @datetime1, @datetime2

>
Eww, magic numbers! And isn't Roy's solution using DatePart() and
DateAdd() a lot cleaner than converting to and from char?

[Roy Harvey]

Re: Set time for UTC

On Thu, 03 Aug 2006 01:24:48 GMT, "Dan Guzman"
<guzmanda@nospa m-online.sbcgloba l.netwrote:

>Whether or not one technique is cleaner than the other is fairly subjective
>but the DATEPART/DATEADD method Roy suggested probably will perform better
>than CONVERT.

I always used to strip off the time by converting to a character
string. I only learned the DATEADD/DATEDIFF approach recently from
reading the SQL Server newsgroups. I am told it is far more
efficient, but I am still trying to decide if it is readable. But I
have had to create some real messes with character strings and
DATEPART and such too.

I still haven't really accepted the simple date arithmatic: SET
@datetime2 = @datetime1 + 2 from your example. I know it works in
days, but I like the explicit unit DAY in DATEADD. I don't even
bother to use the abbreviations in DATEADD, DATEPART or DATENAME any
more, as I find the full word more explicit.

Roy