This project is read-only.
1

Closed

Denver/Phoenix conversion not working properly

description

The next time change for Denver is Nov 2, 2014, 2:00 AM. Verify here: http://www.timeanddate.com/time/change/usa/denver

I tested the following conversion (SQL server local time is Denver):

select cast('2014-11-01 2:00:00 AM' as datetime2) as Denver,
TSqlToolbox.DateTimeUtil.UDF_ConvertLocalToLocalByTimezoneId(8, 7, cast('2014-11-01 2:00:00 AM' as datetime2)) as Phoenix;

Result:
Denver: 2014-11-01 02:00:00.0000000
Phoenix: 2014-11-01 02:00:00.0000000

Expected:
Denver: 2014-11-01 02:00:00.0000000
Phoenix: 2014-11-01 01:00:00.0000000

That is not correct. They should still have a 1 hour difference since Denver doesn't change until the next day (the 2nd).

You can verify here: http://www.timeanddate.com/worldclock/converter.html
Closed Oct 6, 2014 at 9:55 AM by adss
I have analyzed your issue now. And luckily, T-SQL Toolbox works as designed. ;-)

The issue is with your query:

TSqlToolbox.DateTimeUtil.UDF_ConvertLocalToLocalByTimezoneId(8, 7, cast('2014-11-01 2:00:00 AM' as datetime2)) as Phoenix;

You use the wrong Timezone ID for Denver.
You use "8" (=Mountain Standard Time (Mexico)), but Denver is in Timezone with ID "9" (=Mountain Standard Time).

So, your query should be instead:

TSqlToolbox.DateTimeUtil.UDF_ConvertLocalToLocalByTimezoneId(9, 7, cast('2014-11-01 2:00:00 AM' as datetime2)) as Phoenix;

... and then, the query returns your expected result.

comments