convert int to datetime epoch 2

[gtjr921]

I have a date field that is stored as an INT (yes an INT) I hate it,it's a 3rd party db. IT stores the date the same way some unix systems do.
IT stores the number of seconds since the epoch, or 1 January 1970. I am able to convert the int to date, but the time is off. It is actually ahead by 5 hours.
Example for this datetime as an int 1163517315
i get 2006-11-14 15:15:15.000, the correct time should be 10:16 am
the code i used to create my table is

alter TABLE [CDRMAin] add
    [starttimetimecalc] AS
 dateadd(second, endtime, '1 January 1970')

how can i correct this.
thanks

 

[AlexCuse]

I think you need to subtract the number of seconds in five hours from endtime. Try this:

dateadd(second, (endtime - 18000), '1 January 1970')

Hope it helps,

Alex


A wise man once said
"The only thing normal about database guys is their tables".

 

[bborissov]

alter TABLE [CDRMAin] add
    [starttimetimecalc] AS
 DateAdd(hh,-5,dateadd(second, endtime, '1 January 1970'))

?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gtjr921]

bborissov thanks that did it.

 

[bborissov]

Thank you for the star, but Alex code also should works.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP