Post reply

Datetime conversion

  • March 22, 2010 at 1:49 pm

    #218079

    Hi

    I am having a difficulty of converting date time from marel to sql server.

    I am able to convert to the calender date by applying DATEADD function.

    I am not able to get the time part.

    For example, Select DATEADD(day, 40259 - 2, '1901-01-01') gives the year, month, and day. However, I am missing hour, minute, and second part.

    I have attached the actual data in marel and expected column in SQL table.

  • March 22, 2010 at 2:03 pm

    #1137829

    What is "marel"?

    Also, there is no way I am going to open an XLS file posted on the web.

    If you want help, post SQL create table statments and statements to insert the data into that table.

  • March 22, 2010 at 2:47 pm

    #1137848

    Ganesh Lohani (3/22/2010)

    Hi

    I am having a difficulty of converting date time from marel to sql server.

    I am able to convert to the calender date by applying DATEADD function.

    I am not able to get the time part.

    For example, Select DATEADD(day, 40259 - 2, '1901-01-01') gives the year, month, and day. However, I am missing hour, minute, and second part.

    I have attached the actual data in marel and expected column in SQL table.

    You're going to have to specify the time portion with the date:

    Select DateAdd(day, 40259-2, '1901-01-01 18:50:49.333')

    Since you didn't specify a time, it's defaulting to 00:00:00.000

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • March 22, 2010 at 3:19 pm

    #1137873

    I'm lost, even google isn't my friend on this one.

  • March 22, 2010 at 3:22 pm

    #1137875

    Lynn Pettis (3/22/2010)

    I'm lost, even google isn't my friend on this one.

    Agreed. No idea what marel is. No idea how to convert it to datetime. It is in decimal format with some number that appears meaningless.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 22, 2010 at 4:03 pm

    #1137908

    CirquedeSQLeil (3/22/2010)

    Lynn Pettis (3/22/2010)

    I'm lost, even google isn't my friend on this one.

    Agreed. No idea what marel is. No idea how to convert it to datetime. It is in decimal format with some number that appears meaningless.

    Especially since the higher value is an earlier time, like it was counting backward from unknown reference time.

Viewing 6 posts - 1 through 5 (of 5 total)

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