Convert Int to Time

I have a DB that gets telephone call information from a telephone switch. We use 3rd part software and a db to capture all of this. However for some reason most of the fields are stored as int DateTime is stored as an int. I am able to convert most of the date field from int by doing select convert(datetime,convert(varchar(8), localday)) that works fine the int are in the db as 20061029 for 10-29-2006.
How ever I can't convert the columns starttime and endtime. They are also stored as int but
They don't seem to store a date.
For instance for the date 9/29/2006 the start time of one particular call is
1159533056 and the endtime is 1159533264 All i need to get from this is the time since I already have the date from another column. I can't figure out what this data represents.
How can I convert this to datetime?

Thanks!

Quote:

"The datetime and smalldatetime data types are stored internally as integers. The datetime data type is stored as a pair of four-bytes integers, which together represent the number of milliseconds since midnight of January 1, 1753. The first four bytes store the date, and the second four bytes store the time. The smalldatetime data type is stored as a pair of two-byte integers, which together represent the number of minutes since midnight on January 1, 1900. The first two bytes store the date, and the second two bytes store the time."

Also, check out this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp (see the first table on that page).

So, if your columns' data types are datetime or smalldatetime (but not "int", which would be bizarre), you don't need to concern yourself with converting it manually and/or trying to figure out what exactly this integer means. Just call the appropriate version of CONVERT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp).

|||

It looks to me like your integers are storing number of seconds since the epoch, or 1 January 1970. This is pretty common on UNIX systems. (Cf. http://en.wikipedia.org/wiki/Unix_time)

Try:

declare @.d1 datetime
declare @.d2 datetime
set @.d1 = dateadd(second, 1159533056, '1 January 1970')
set @.d2 = dateadd(second, 1159533264, '1 January 1970')
print @.d1
print @.d2

Result:

Sep 29 2006 12:30PM
Sep 29 2006 12:34PM

Cheers,
-Isaac

|||

Great that did it. yes they are stored as integers. I hate this db everything that is not text is stored as INT I mean Everything.
I have had to create several calculated fields for them. It's 3rd party db if i modify existing colums it won't function.

What would be the best way to create a calculated field for this data?
Thanks again

|||nevermind i got it by doing this...

alter TABLE [CDRMAin] add
[starttimetimecalc] AS dateadd(second, endtime, '1 January 1970')|||I just realized that this code is giving me the proper date from my date that is stored as an int, but it is not giving me the proper time
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