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 |
|
marcuscoker
Starting Member
13 Posts |
 Posted - 2006-08-30 : 09:21:28
|
| HiWas wondering if anybody could help meI have a date field in an off the shelf application that uses SQL as a backendThe date field is in the UTC timecode format ie seconds since 01/01/1970 (i think)Does anybody know how i could go about converting this 1142322277 into a date dd/mm/yyyyThanksMarcus |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-30 : 09:23:51
|
| [code]select dateadd(second, 1142322277, '19700101'), convert(varchar, dateadd(second, 1142322277, '19700101'), 103)[/code]Peter LarssonHelsingborg, Sweden |
 |
|
|
marcuscoker
Starting Member
13 Posts |
Posted - 2006-08-30 : 09:33:27
|
| Many thanks PeterJust tried, converts the dateThank you |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2006-08-30 : 09:44:36
|
| If it's really the number of UTC seconds, wouldn't you have to take account of leap-seconds? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-30 : 09:48:33
|
| Yes, you can have a table for which times a leap second occured.A leap second can be either positive or negative depending on the Earth's rotation. Since the first leap second in 1972, all leap seconds have been positive and there were 23 leap seconds in the 34 years to January, 2006. This pattern reflects the general slowing trend of the Earth due to tidal braking.I think MVJ can provide more information about this.Also see [url]http://en.wikipedia.org/wiki/Leap_second[/url].Peter LarssonHelsingborg, Sweden |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-30 : 11:38:30
|
| My guess is that it is not UTC time, but 32-bit UNIX time.I believe that most UNIX systems handle the leap second issue with the simple method of ignoring them.This external link explains how time is calculated on UNIX systems.Unix Time:http://en.wikipedia.org/wiki/Unix_timeCODO ERGO SUM |
|
|
|
|
|
|
|
|
UTC timecode convert