Convert a numeric value to a valid date time

  • Hi All,

    Primary Task :- To import .txt file to sql server 2005.

    Challenge :-There is a field(Closed_date) in the .txt file which contains the value like "1205894254" which is equal to "19-Mar-08".

    Historic approach to know the value of closed_date:- We used to export that .txt file to excel.

    We used to apply a formula on closed_date field which is equal to

    25569 + value/86400 .

    For eg see the below table to see the equivalent date of closed_date column.

    close_date Date

    1205894254 19-Mar-08

    1205895171 19-Mar-08

    1205808001 18-Mar-08

    1205894933 19-Mar-08

    1205980687 20-Mar-08

    1205722185 17-Mar-08

    1205724847 17-Mar-08

    Need Solution for:-How to convert "closed_date" to a valid date format.

    Any help to this ould be really appreciated.

    Thanks in advance and have a Great Day !!

    Regards,

    Sriram Satish

  • Looks like the data is the number of seconds since 1/1/1970 so using the DateAdd function should work. Look up DateAdd on BOL for details.

  • The F_UNIX_TIME_TO_DATETIME function on the link below will do what you want.

    UNIX Time Conversion Functions

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66858

    The code is simple, so you can also do it this way:

    select

    dateadd(ss,a.TIME_IN_SECONDS,25567)

    from

    MyTable a

  • This will also work with you current formula, just by adjusting it by 2 days:

    Select cast(25567 + 1205894254/86400 as datetime)

    union all Select cast(25567 + 1205895171/86400 as datetime)

    union all Select cast(25567 + 1205808001/86400 as datetime)

    union all Select cast(25567 + 1205894933/86400 as datetime)

    union all Select cast(25567 + 1205980687/86400 as datetime)

    union all Select cast(25567 + 1205722185/86400 as datetime)

    union all Select cast(25567 + 1205724847/86400 as datetime)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

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