Link to home
Start Free TrialLog in
Avatar of jasonbrandt3
jasonbrandt3

asked on

Help converting unix datestamp to sql 2005 datetime

I've found some functions to convert a unix timestampt (bigint) to a SQL 2005 datetime.  I'm using the following code:

select dateadd(ss, startdate/1000, '1970-01-01') as DateStarted from mdl_course

Thats fine, except these aren't the results aren't exactly what I was looking for:

1970-01-15 06:08:56.000

I know these are dates that should begin in 2006.  Not sure how to get these to the actual date.

The data in the column I'm querying is as follows:
1137474000

Thanks for any assistance!
Avatar of Hwkranger
Hwkranger
Flag of United States of America image

Below is an example:
SELECT
 *
FROM
 [YOUR TABLE]
WHERE
  Convert(datetime, dateadd(s, ,<YOUR DATE TIME COLUMN IN UNIX>, '19700101')) >= dateadd(dd, -30, getdate())
 
SELECT
 DATEDIFF(s, '19700101', Dateadd(dd, -30,GETDATE())) 

Open in new window

Avatar of jasonbrandt3
jasonbrandt3

ASKER

I'll give it a shot!  Let you know shortly.
ASKER CERTIFIED SOLUTION
Avatar of Hwkranger
Hwkranger
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SELECT
 Convert(datetime, dateadd(s, 1137474000, '19700101'))

Returns

2006-01-17 05:00:00.000

FYI.
Perfect!  Thanks for the help!