Help converting unix datestamp to sql 2005 datetime

jasonbrandt3
jasonbrandt3 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Hwkranger

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

jasonbrandt3

Author

Commented:
I'll give it a shot!  Let you know shortly.
Commented:
I mis-pasted what i wanted to paste.

here are examples
-- Change today to unix
SELECT
 DATEDIFF(s, '19700101', Dateadd(dd, -30,GETDATE())) 
 
-- change unix to today
// This is an example of a UNIX timestamp for the date/time 11-04-2005 09:25.
SELECT
 Convert(datetime, dateadd(s, 1113211532, '19700101'))

Open in new window

Hwkranger

Commented:
SELECT
 Convert(datetime, dateadd(s, 1137474000, '19700101'))

Returns

2006-01-17 05:00:00.000

FYI.
jasonbrandt3

Author

Commented:
Perfect!  Thanks for the help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial