Convertion of integer to Datetime format

  • harishchede

    SSC-Addicted

    Points: 401

    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

  • arun.sas

    SSChampion

    Points: 11831

    Hi,

    Are you sure this int column having 10 digit characters?

  • Dave Ballantyne

    SSC-Dedicated

    Points: 33556

    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]

  • arun.sas

    SSChampion

    Points: 11831

    Hi,

    try this

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

  • arun.sas

    SSChampion

    Points: 11831

    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

  • harishchede

    SSC-Addicted

    Points: 401

    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

    SSC-Addicted

    Points: 401

    🙂

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

  • Lowell

    SSC Guru

    Points: 323442

    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!

  • MothInTheMachine

    Hall of Fame

    Points: 3238

    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!

    Love them all ... regardless.
    - Buddha

  • Lowell

    SSC Guru

    Points: 323442

    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!

  • Lynn Pettis

    SSC Guru

    Points: 442180

    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.

  • Dave Ballantyne

    SSC-Dedicated

    Points: 33556

    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]

  • texpic

    SSCertifiable

    Points: 5882

    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?

  • hamed.alzubide

    SSC Rookie

    Points: 29

    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

  • Luis Cazares

    SSC Guru

    Points: 183583

    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 15 (of 15 total)

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