Convertion of integer to Datetime format

  • Hello 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 format

  • Hi,

    Are you sure this int column having 10 digit characters?

  • At a guess , that is the number of seconds from 01jan1970

    try this

    select (1231151827/60)

    select 20519197/60

    select 341986/24

    select dateadd(dd,14249,'19700101')



    Clear Sky SQL
    My Blog[/url]

  • Hi,

    try this

    select convert(datetime,40010/*your int value*/,112/*your format*/)

  • DATETIME to INT

    declare @time1 datetime

    set @time1 = getdate()

    select convert(int,@time1)

    print @time1

    INT to DATETIME

    declare @time int

    set @time = 40042

    select convert(datetime,@time,112)

    print @time

  • Thanks a lot it worked

    thanks to all who supported

    it got resolved by

    putting the query

    select (1231151827/60)

    select 20519197/60

    select 341986/24

    select dateadd(dd,14249,'19700101')

  • 🙂

    harishchede (8/18/2009)


    Thanks a lot it worked

    thanks to all who supported

    it got resolved by

    putting the query

    select (1231151827/60)

    select 20519197/60

    select 341986/24

    select dateadd(dd,14249,'19700101')

  • Dave 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:

    select dateadd(dd,14249,'19700101') --add the offset for the stored date

    --2009-01-05 00:00:00.000

    select (1231151827 % 60) --sec to min gain 7 seconds from

    select 20519197 % 60 --min to hour gain 37 minutes

    select 341986 % 24 --hour to days gain 10 hours

    select dateadd(second,1231151827,'19700101')

    --2009-01-05 10:37:07.000

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • That 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!

    Even as a mother protects with her life
    Her child, her only child,
    So with a boundless heart
    Should one cherish all living beings;

  • 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?)

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (8/19/2009)


    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?)

    IIRC, this is how *nix stores date/time values.

  • Lynn Pettis (8/19/2009)

    IIRC, this is how *nix stores date/time values.

    Yup , no magic mystical powers involved 🙂

    http://en.wikipedia.org/wiki/Unix_time



    Clear Sky SQL
    My Blog[/url]

  • I'm assuming the Unix server is always storing this in the time zone UTC, so if I want it in CST it is always -6 hours?

  • Lowell - Tuesday, August 18, 2009 6:11 AM

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

    very good
    work done but i have other problem 
    i wanna separate the output into tow column  like this 

        date                time     
    19-4-2018       08:59:17

    i used ur statement       ,dateadd(second,nDateTime,'19700101') as Ndate

    the output of this
    --------------------------------------
    2018-04-19 08:59:17.000

    please help me

    thank u

  • hamed.alzubide - Thursday, April 19, 2018 2:37 AM

    Lowell - Tuesday, August 18, 2009 6:11 AM

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

    very good
    work done but i have other problem 
    i wanna separate the output into tow column  like this 

        date                time     
    19-4-2018       08:59:17

    i used ur statement       ,dateadd(second,nDateTime,'19700101') as Ndate

    the output of this
    --------------------------------------
    2018-04-19 08:59:17.000

    please help me

    thank u

    You are hijacking a thread when you should have opened yours. However, I will grant you recognition from possibly searching an answer before posting.
    Here's an article that shows some options that you have , along with a performance comparison among them.
    http://www.sqlservercentral.com/blogs/dwainsql/2015/08/28/the-fastest-way-to-combine-date-and-time-data-types-to-a-datetime/
    The best solution also depends on the data types that you're using as input and output.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply