Problem with Query (DATEADD) 2

[chuckh70]

Hello,

I am using the below string to pull the date out of our database which is in unix format, and convert it to something the user can read.

I used the following which works fine.

DATEADD(dd, Create_Time / 86400, '1/1/1970')

I found out yesterday that all of the records are 8 hours ahead, and after looking into the Application the use stores the date as GMT and converts it to the proper time in the application. I tried just wrapping another DATEADD around my current one that would just subtract 8 hours which I am having problems with, and I also realized I am stripping the time off. If I use the below I get datetime is invalid for argument 2 of dateadd.

DATEADD(hh, DATEADD(dd, Create_Time / 86400, '1/1/1970'),-8)

 

[vongrunt]

UNIX date is represented as number of seconds since 1970-01-01 so... why not using:

DATEADD(ss, (Create_Time -8*3600), '1/1/1970')

... then strip time off if necessary?

 

[SQLBill]

The reason you are getting the invalid argument is that you are using -8 as argument 2. It should be argument 1.

-SQLBill

 

[chuckh70]

Perfect thanks all.