Error converting datetime...... ".....out-of-range datetime value..."

  • Hello, can you help me with the following error. This error appears to me when converting varchar to datetime. The idea is that the output being as follow (dd-mm-yyyy)

    select CONVERT(DATETIME,CAST( (CAST(DAY(getdate()) AS VARCHAR) + '-' + CAST(MONTH(getdate()) AS VARCHAR) + '-' + CAST(YEAR(getdate()) AS VARCHAR)) AS DATETIME),102)

    The following error appear:

    Msg 242, Level 16, State 3, Line 1

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Thanks and regards,

    JMSM 😉

  • You'll need to cast it as a varchar and not a datetime.

    select CONVERT(varchar(10),CAST( (CAST(DAY(getdate()) AS VARCHAR) + '-' + CAST(MONTH(getdate()) AS VARCHAR) + '-' + CAST(YEAR(getdate()) AS VARCHAR)) AS varchar),102)

  • Thanks for your answer, but i've got three other question that i appreciate if you can ask me.

    Q1:

    Why when i've changed the query the one that is write down errors didn't were reported?

    select convert(datetime,CAST( (CAST(DAY(getdate()) AS VARCHAR))as varchar)+ '-' + CAST(MONTH(getdate()) AS VARCHAR) + '-'+ CAST(YEAR(getdate())AS VARCHAR),103)

    Q2: Whats the difference between the one that i write and the one that you send me?

    select convert(varchar(10),cast( (cast(day(getdate()) as varchar) + '-' + cast(month(getdate()) as varchar) + '-' + cast(year(getdate()) as varchar)) as varchar),102)

    Q3: Why should i must cast it as varchar and not as datetime?

    Thanks and regards,

    JMSM 😉

  • JMSM

    Try experimenting with the date style parameter in the CONVERT function. This may prevent the "out of range" error eppearing.

    John

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

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