Converting C++ Unix time_t Julian date to SQL date

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!
andytHi 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
"Andy Turner" wrote:

> 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
>|||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
--