Convert Time zonoffset value to varchar

Question

Query:

DECLARE @TimeZoneOffset datetimeoffset
SELECT @TimeZoneOffset  = Time_Zone_Offset 
FROM OFFSET_TABLE WHERE Active=1

Time_Zone_Offset column contains value like -6:00 (only offset)

When I do SELECT @TimeZoneOffset it throws me an error

Conversion failed when converting date and/or time from character string.

I know I am doing something wrong. I may need to CONVERT/CAST but can't get o/p so far.

Any help

Answer 1

To visualize what is happening here, try this:

DECLARE @x VARCHAR;
SET @x = 'abcdefghijklmnop';
SELECT @x;

Result:

----------
a

You have silently lost data from your variable, because you didn't bother declaring a length for your VARCHAR. In your case, I think you are ending up trying to use the string - somewhere, as opposed to the string -6:00.

I'm not sure how a simple SELECT yielded the error you mentioned; I suspect you are using it in some other context you haven't shown. But please try it again once your variable has been declared correctly.

Now I see why, your question wasn't correct - you said you were converting to VARCHAR but you weren't. This is not really unexpected, as -6:00 is not a valid DATETIMEOFFSET value; there is expected to be date and time components as well, otherwise the data type would just be called OFFSET. A valid DATETIMEOFFSET, according to the documentation, is:

DECLARE @d DATETIMEOFFSET = '1998-09-20 7:45:50.71345 -05:00';

So perhaps you have some datetime value and you want to apply the offset, well you can use SWITCHOFFSET() for that. However -6:00 is not a valid value; it needs to be in [+/-]hh:mm format (notice the leading 0 above, which seems to be missing from your sample data). So this would be valid:

DECLARE @datetime DATETIME = GETDATE(), @offset VARCHAR(6) = '-06:00';
SELECT SWITCHOFFSET(@datetime, @offset);

You need to correct the data in your offsets table and you need to change the way you are using the output. Personally, I've found it easier to stay away from DATETIMEOFFSET and SWITCHOFFSET(), especially since they are not DST-aware. I've had much better luck using a calendar table for offsets, storing the offsets in minutes, and using DATEADD to switch between time zones. YMMV.