InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 12:24:47 GMT20http://www.sqlservercentral.com/Forums/Topic772480-361-1.aspx[quote][b]Lynn Pettis (8/19/2009)[/b]IIRC, this is how *nix stores date/time values.[/quote]Yup , no magic mystical powers involved :-)[url]http://en.wikipedia.org/wiki/Unix_time[/url]Wed, 19 Aug 2009 09:36:44 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic772480-361-1.aspx[quote][b]Lowell (8/19/2009)[/b][hr]After Dave inferred that it was the # seconds since 01/01/1970, it was pretty easy...but how the heck did he figure out the starting date? Dave had you tripped over the same issue before? is it a common conversion from some other system(ie Oracle or DB2 or something?)[/quote]IIRC, this is how *nix stores date/time values.Wed, 19 Aug 2009 09:28:39 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic772480-361-1.aspxAfter Dave inferred that it was the # seconds since 01/01/1970, it was pretty easy...but how the heck did he figure out the starting date? Dave had you tripped over the same issue before? is it a common conversion from some other system(ie Oracle or DB2 or something?)Wed, 19 Aug 2009 09:25:07 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic772480-361-1.aspxThat is a pretty interesting problem. I actually discovered that I have a similar problem in a db I just acquired so these solutions will come in handy for me as welL!Wed, 19 Aug 2009 09:14:43 GMTMothInTheMachinehttp://www.sqlservercentral.com/Forums/Topic772480-361-1.aspxDave Ballentine showed you step by step how the conversion is going to work; but if you use the final answer, you don't get the timne portion, just the correct date;you lose the modulus of the integer division;you should shortcut and just use the DATEADD function to add the total seconds:[code]select dateadd(dd,14249,'19700101') --add the offset for the stored date--2009-01-05 00:00:00.000select (1231151827 % 60) --sec to min gain 7 seconds fromselect 20519197 % 60 --min to hour gain 37 minutesselect 341986 % 24 --hour to days gain 10 hoursselect dateadd(second,1231151827,'19700101')--2009-01-05 10:37:07.000[/code]Tue, 18 Aug 2009 06:11:41 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic772480-361-1.aspx:-)[quote][b]harishchede (8/18/2009)[/b][hr]Thanks a lot it worked thanks to all who supported it got resolved by putting the queryselect (1231151827/60)select 20519197/60select 341986/24select dateadd(dd,14249,'19700101')[/quote]Tue, 18 Aug 2009 04:50:25 GMTharishchedehttp://www.sqlservercentral.com/Forums/Topic772480-361-1.aspxThanks a lot it worked thanks to all who supported it got resolved by putting the queryselect (1231151827/60)select 20519197/60select 341986/24select dateadd(dd,14249,'19700101')Tue, 18 Aug 2009 04:49:37 GMTharishchedehttp://www.sqlservercentral.com/Forums/Topic772480-361-1.aspxDATETIME to INT[code]declare @time1 datetimeset @time1 = getdate()select convert(int,@time1)print @time1[/code]INT to DATETIME[code]declare @time intset @time = 40042select convert(datetime,@time,112)print @time[/code]Tue, 18 Aug 2009 03:04:38 GMTarun.sashttp://www.sqlservercentral.com/Forums/Topic772480-361-1.aspxHi,try this[code]select convert(datetime,40010/*your int value*/,112/*your format*/)[/code]Tue, 18 Aug 2009 02:59:22 GMTarun.sashttp://www.sqlservercentral.com/Forums/Topic772480-361-1.aspxAt a guess , that is the number of seconds from 01jan1970try this[code]select (1231151827/60)select 20519197/60select 341986/24select dateadd(dd,14249,'19700101')[/code]Tue, 18 Aug 2009 02:55:32 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic772480-361-1.aspxHi,Are you sure this int column having 10 digit characters?Tue, 18 Aug 2009 02:50:54 GMTarun.sashttp://www.sqlservercentral.com/Forums/Topic772480-361-1.aspxHello All One of the column in my database contains createdate which gets stored in integer format just like that 1231151827 i want to get it converted into datetime formatTue, 18 Aug 2009 02:33:48 GMTharishchede