sql server data warehouse: Converting C++ Unix time_t Julian date to SQL date -- sql server data warehouse

Converting C++ Unix time_t Julian date to SQL date

Andy Turner
7/26/2004 10:46:05 AM

sql server data warehouse:

I've just been trying to do this. I looked on Google and it seems to
be a common problem with no obvious solution. I've seen various
solutions which don't seem exactly elegant, so I figured I'd post the
solution I came up with. It's effectively a single line solution,
albeit with various embedded calls. It makes an adjustment for the
local timezone too!

DECLARE @time_t INT
SET @time_t = 1090834321 /* 10:32am, July 26th 2004 BST */

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

Hope that helps. If there's a problem with it, let me know!

andyt

RE: Converting C++ Unix time_t Julian date to SQL date

Noma-Gcina
10/20/2004 3:33:03 PM

Hi Andy,
I have received a data file and it has julian dates. How Do I convert them
to normal dates? I tried this code and replaced the variable with the date I
received, and All it did was give me the same date with different times.

Any ideas? What does the hardcoded '1970-01-01' do?

Thanks
Noma-Gcina
nmtshontshi@deloitte.com

[quoted text, click to view]

Re: Converting C++ Unix time_t Julian date to SQL date

David Portas
10/20/2004 11:41:09 PM

The Unix timestamp value represents the number of seconds since 1970-01-01.
You can convert it like this:

DECLARE @ts INTEGER
SET @ts = 1098230400

SELECT DATEADD(SECOND,@ts,'19700101')

(This isn't a Julian Date BTW)

--
David Portas
SQL Server MVP
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server data warehouse group.