Post reply

Date format conversion

  • June 17, 2014 at 3:30 am

    #292496

    Hello,

    i am trying to convert a string like this 'le dd/mm/yyyy' into a datetime.

    I have removed the 'le ' part and used covert(datetime, 'dd/mm/yyyy',103) to convert into datetime. This works for example for 'le 22/11/1799' but for 'le 09/11/1716' it does not work.

    select convert(datetime,RIGHT('le 22/11/1799', LEN('le 22/11/1799') - 3), 103) -> it works

    select convert(datetime,RIGHT('le 09/11/1716', LEN('le 09/11/1716') - 3), 103) -> it does not work

    Could there be an issue with the year?

    Thank so much for any help or ideas!

  • June 17, 2014 at 3:35 am

    #1722198

    Datetime only goes back to 1753 (which is for most business far enough in the past :-D).

    Try using the datetime2 data type (that one goes back to the year 0).

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 17, 2014 at 3:37 am

    #1722199

    What Koen said.

    Yesterday I was wondering why we had some call data with a 1753 date on it. I thought it was our problem until I looked up that.

    Edited to make sense!

    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • June 17, 2014 at 3:47 am

    #1722204

    Thanks so much for your answers! It worked great!

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

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