time change

[angela4eva]

i have stored procedure that enters the time stamp.but it getting the timestamp of i server blos when i inserted a record here it was 12:59 pm here and the the timestamp in the database shows 11:59pm.I am usinga stored procedure.what is the best way to fix this.i guess this is happening bcos of daylight saving as our servers are in usa and the website is used only in panama(there is no day light saving here).Is it possible to chenge the server time to be always a particular time zone
thanks

 

[SQLSister]

When I have to deal with time zone issues I store everything in UTC time. Look at getUTCDate as a function. YOu can then always display the date inthetime zone of choice by converting it. But all are starting from the same place = so if two records say 11:00 am they were entered at the same time no matter whcih time zone it came from because it is in Greenwhich Mean time inthe DB.

Questions about posting. See faq183-874

 

[angela4eva]

ok thanks sqlsister,
But here is my question i am trying to query the database to get all records entered between 11/11/2006 pm and 11/12/2006 pm panama time.how can i find this out.bcos i entered a record after 11/12/2006 12:30pm panam time and the queries for all records entered >=11/11/2006 12pm and <11/12/2006 pm ,the last record i entered also showed up
bcos i queries the server and the time of the record i entered was stamped as 11/12/2006 11:30pm.
please help

 

[SQLSister]

If you did not store the datetimes orginally all inthe UTC time, then you can;t get the data out for a specific timezone without knowing the time zone of the datetime that the data was stored in. If all are in one timezone 9say Eastern Standard time) becasue that is the time zone on the server, then you can use date math to figure the datetime for a differnt timezone. But if the data is dependent on the datetime where the user was who entered it, then it would be impossible to know what time zone it was enterd in.

But assumming the first case, then look at DateAdd in BOL to see how to convert the time to the correct timezone. You'll need to know the number of hours differnce between the two time zones and then you will have it.

Questions about posting. See faq183-874