Date/Time Function
-
Good Morning Everyone,
I have written store procedure to update/insert data to existing table. Each time procedure is excuted i'm using GetDate() function to store the value in table. Since our server is located at some other state then where our company is located......i'm getting one hr differnt in time zone.
Is there any Date/Time function i can use so that i can get current local time when procedure gets executed ?
Thanks In Advance
-
USE GMT and pass the hour offset to do the correction.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff,
I used GetUtcDate() function but it did not return the correct date/time. I need to retrive Eastern Time.
Thanks
-
Hmmm...
Quite tricky.
Don't have even a clue how to get Eastern Time from GMT...
Need to be PhD to do this.
_____________
Code for TallyGenerator -
Sohil...
You do realize that UTC is based on the time at the Prime Meridian and that you need to subtract some hours from that to get Eastern Time, right?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Sohil,
Eastern Daylight Time (EDT) is GMT minus 4 hours, Eastern Standard Time (EST) is GMT minus 5 hours. If both the server time zone and the company location timezone participate in daylight savings time (DST), it would be simpler to add or subtract the time difference when you store the GetDate(). For example, if you server is Central time, and you want Eastern time, use:
DateAdd(hh, 1, GetDate())
Some areas do not participate in DST, so in that case, you would have to take additional steps.
-
Wait a minute, fellas. Although I have never used GMT, it has always been my understanding that this was what one used to get away from all this time calculation. That is, when I write a timestamp of, say, 14:00:00 to my database in Central, and someone in Eastern reads it, he sees 15:00:00 because that's what time it was in his TZ when the record was written.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
DateAdd(hh,1,GetDate()) work fine
Thanks for all your help
-
Pretty sure thats not the way it works, Tomm. If it's 14:00 in central and GMT is used to write the time, the time that will be written is 20:00 (Greenwich Mean Time) and no matter which time zone it is observed in, the database will contain 20:00. In order to see it in the correct time, you have to add the local time zone offset to the GMT to see the local time. For Central, it would be -6:00... for Eastern, it would be -5:00. You also have to take into account whether or not daylight savings time is in effect and how much of an offset that is (it's not always an hour depending on the locality).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
You also have to consider what the offset to UTC was at the time the data was written. If the data you are looking at is from January, then the offset to UTC for Eastern time was -5, but for June it is -4.
Also, the correct term is UTC (Coordinated Universal Time), not GMT. Coordinated Universal Time (UTC) is a high-precision atomic time standard, while Greenwich Mean Time (GMT) is a term originally referring to mean solar time at the Royal Observatory, Greenwich in England, although it is common to use GMT to mean UTC.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply