Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pithhelmet
Posting Yak Master
183 Posts |
 Posted - 2004-03-01 : 14:20:29
|
| Hi Everyone -I am receiving data from a GPS system, and the time comes in as GMT and i need to save it as the timezone specified in another field in another table on the same database (company_table.time_offset)I was thinking of putting a trigger on the insert of the unit_history_table as the data comes in...but i could also handle the conversion operation on the processing application - but if i can accomplish on the MSSQL server - so much the better....The company_table.time_offset is the actual value... so EST is -5 in the table...thanks for your advisetake caretony |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-01 : 14:23:40
|
| Search this site for udf_Timezone_Conversion. It's a UDF that I wrote that can convert from one timezone to another. It also handles daylight savings time. It uses two other UDFs and one table that has the timezone that are supported in it in that table.We keep all of our data in GMT as we support customers from different timezones. We then use the UDF to display the appropriate converted time to the user. If you really need the data in the correct timezone, yes you can use a trigger that uses this UDF. It is important to use this UDF or something like it so that it handles daylight savings time. -5 is the offset for EST when not in daylight savings time. -4 if I recall correctly is the offset when in daylight savings time.EDIT: Found the link to the UDF:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30335Tara |
 |
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2004-03-02 : 08:55:41
|
| Hi Tara ----Thank you x1000000000000000000I have been beating myself to death trying accomplish in a Delphi application that runs on the server itself -but due to a "feature" of Delphi - the datetimetostr function returns the date string "corrected" based on the system timezone value of the machine it is running on.... Borland says it will correct this feature in a future release... I will implement your solution right now!!!thanks again for all your help!!take caretony |
|
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2004-03-02 : 20:17:58
|
| Once again Tara ----Thank You VERY much - you really saved my bacon!take caretony |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-03-03 : 09:07:49
|
| So are you storing the data converted to timezone or as GMT. I would argue for storing it like Tara does, it has less integrity issues. |
|
|
|
pithhelmet
Posting Yak Master
183 Posts |
Posted - 2004-03-03 : 09:49:36
|
| Hi Crazyjoe and everyone -The data that comes from the GPS system is broken apart at the gateway,and is written to the database in two different fields.I do have a timestamp on the table that is populated with thegetdate() routine whenever the data is written - but that is purelya "note to programmer" field - not used anywhere else.thanks for the replytake caretony |
|
|
|
|
|
|
Changing from GMT to Another time zone