Daylight Savings ERROR

I'm working with cities in different Time Zones and am trying to get the Local Time of a city based on the server timestamp, hour difference from that server, a Daylight Savings Flag (0 - does NOT observe DLS, 1 - DOES observe DLS), and comparison of the computed Local Time to the dates of Daylight Savings Begin and End. Most of the calculations are working properly, but I am having a problem with a few test cases. The server is in Los Angeles and the test case is…

(Hawaii): Hour difference:-2; Daylight Savings Flag: 0; Server Time: 5:00am on 4/3/05 (Day that Daylight Savings occurs)

The expected output is 2:00am. (Hawaii is 2 hours behind Los Angeles, which would make Hawaii time 3:00am in Standard Time, but since Hawaii does NOT observe DLS, it does not spring forward making the time in DLS 2:00am.) When I run my function in SQL Server I get this correct output (2:00am), however, Cold Fusion is automatically moving the hour up to 3:00am. How can I turn this auto-convert for DLS time off? Shouldn’t the time just be “as is”. If I say 4:00am is the time I want, I mean 4:00am. This doesn’t mean that if I ask the time in Daylight Savings Time it should spring up to 5:00am. I don’t believe this should happen at 2:00am on the day the clocks change because the server itself should be making that conversion.

Not using my functions or any other formatting functions, I have included some test cases at the end to illustrate my point.

Thank you for any help & Any suggestions are welcome.
--Matt

PS. When I try CAST(CAST('04/03/2005 2:00:00 AM' AS DATETIME) AS VARCHAR) I get the correct result (2:00AM), however, I MUST insert a field with datatype DATETIME into the Database.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When running:

<CFQUERY NAME="getLocal1"
DATASOURCE="#***#"
USERNAME="#***#"
PASSWORD="#***#">
SELECT CAST('04/03/2005 2:00:00 AM' AS DATETIME) AS myTime</CFQUERY>

<CFOUTPUT>#getLocal1.myTime#</CFOUTPUT>

which is the moment Daylight Savings becomes in effect on the server I get "2005-04-03 03:00:00.0" as my output. (INCORRECT - There seems to be an auto-convert in effect.)


When running:

<CFQUERY NAME="getLocal2"
DATASOURCE="#***#"
USERNAME="#***#"
PASSWORD="#***#">
SELECT CAST('04/02/2005 2:00:00 AM' AS DATETIME) AS myTime</CFQUERY>

<CFOUTPUT>#getLocal2.myTime#</CFOUTPUT>

which is the day before Daylight Savings becomes in effect I get "2005-04-02 02:00:00.0" as my output. (CORRECT)


When running:

<CFQUERY NAME="getLocal3"
DATASOURCE="#***#"
USERNAME="#***#"
PASSWORD="#***#">
SELECT CAST('04/03/2005 3:00:00 AM' AS DATETIME) AS myTime</CFQUERY>

<CFOUTPUT>#getLocal3.myTime#</CFOUTPUT>

which is an hour AFTER Daylight Savings occurred I get "2005-04-02 03:00:00.0" as my output. (CORRECT)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You might want to ask this in the SQL forum or in a forum dedicated to your specific database. This doesn't really have anything to do with ColdFusion, and since each database handles dates and times in their own way, with their own functions, I think you'd get a better response in a forum specific to the database type.

Thanks for the suggestion. I thought, however, that since I am getting the correct time when I run my query directly in the SQL Server database that the problem may actually be in ColdFusion...

SELECT CAST('04/03/2005 2:00:00 AM' AS DATETIME) in SQLServer retuns 2:00am (CORRECT)

SELECT CAST('04/03/2005 2:00:00 AM' AS DATETIME) in a ColdFusion file returns 3:00am (INCORRECT)