Post reply

Convertion of integer to Datetime format

  • August 18, 2009 at 2:33 am

    #213683

    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

  • August 18, 2009 at 2:50 am

    #1040674

    Hi,

    Are you sure this int column having 10 digit characters?

  • August 18, 2009 at 2:55 am

    #1040680

    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]

  • August 18, 2009 at 2:59 am

    #1040681

    Hi,

    try this

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

  • August 18, 2009 at 3:04 am

    #1040686

    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

  • August 18, 2009 at 4:49 am

    #1040724

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

  • August 18, 2009 at 4:50 am

    #1040727

    🙂

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

  • August 18, 2009 at 6:11 am

    #1040771

    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!

  • August 19, 2009 at 9:14 am

    #1041629

    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;

  • August 19, 2009 at 9:25 am

    #1041643

    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!

  • August 19, 2009 at 9:28 am

    #1041648

    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.

  • August 19, 2009 at 9:36 am

    #1041660

    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]

  • May 28, 2013 at 1:07 am

    #1618869

    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?

  • April 19, 2018 at 2:37 am

    #1987462

    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

  • April 19, 2018 at 6:35 am

    #1987494

    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