Convert UNIX time to SQL . - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert UNIX time to SQL .

Author

Topic

admin001
Posting Yak Master

166 Posts

Posted - 2004-09-13 : 01:23:50

Hi ,

We are in the process on deploying a new application and we figured out the application generates date field in UNIX format something like " 1031817600 "

I was trying to get rid ouf this issue but somehow it seems that there is no way we can convert this timestamp to a readable format .

I tried the query below as

DECLARE @time_t int
SET @time_t = 1031817600

DECLARE @timestamp DATETIME
SET @timestamp = dateadd(ss,
datediff(ss, GetDate(),
dateadd (ss, @time_t , '19700101')
),
@time_t
)

PRINT @timestamp
GO

But unfortunately it generates an overflow . Any thoughts on how to get this thing sorted out or alternative ways to coverting the UNIX timestamp to SQL ?

Thank you very much .

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-09-13 : 03:41:37

I don't understand why you're trying to use 3 functions to get the date. Surely you only need this:

DECLARE @time_t int
SET @time_t = 1095061393

DECLARE @timestamp DATETIME
SET @timestamp = dateadd (ss, @time_t , '19700101')

PRINT @timestamp
GO

That should be good to 19 Jan 2038, at which point the world will end anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources