Adding timezones to hour fields

[twifosp]

I have a column which gives me the GMT hour that a given activity occured in.

so
00
01
02
..
22
23

I have a timezone that the action occured in so 0 for GMT, -5 for EST ect. I need to create an output that gives the hour of the local hour that the action occured in.

Since this GMT hour column is stored in an int function, I can't quite grasp how to convert it to a datetime so I can do dateadd functions on it.

I can't quite grasp how to create a formula for this either since the timezone can be either positive or negative.

I guess I could use a case statement, but I know there is a clean formula that can be applied here.

 

[twifosp]

Nevermind, I got it. Simple as putting the rest of the date string on the column so I CAN use dateadd function. Duh.

substring(convert(varchar(20),dateadd(hh,cast(timezone as int), cast((hour + ':00:00.000') as datetime)),8),1,2)

 

[ESquared]

I'm sure you've thought of this, but without a date associated the GMT hour becomes pretty meaningless. In any case, you don't have to do any conversion to datetime at all to get the hour adjusted properly:

convert(varchar(2), (convert(int, timezone) + 24 - convert(int, hour)) % 24, 2)

I do have a couple of questions, though. Why are numeric values such as timezone and hour being stored in character columns? It seems not best practice to me.

Second, why isn't the hour simply stored as (even better) a datetime or smalldatetime value? Then you could simply do

dateadd(hh, timezone, GMTHour)
-- or
datepart(hh, dateadd(hh, timezone, GMTHour))

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]