Post reply

Date Conversion error: out of range

  • December 9, 2013 at 1:39 am

    #302123

    I have this query

    DECLARE @dt VARCHAR(20)

    SET @dt = cast(MONTH(getdate()) as varchar(20)) + cast(YEAR(getdate()) as varchar(20))

    SELECT CONVERT(DATETIME, '01' + @dt, 112) as [date]

    I get this error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. please help

  • December 9, 2013 at 1:48 am

    #1671822

    If you add [font="Courier New"]SELECT @dt[/font] to your code, you'll see why it's failing.

    John

  • December 9, 2013 at 1:53 am

    #1671824

    Actually it was supposed to be SELECT CONVERT(DATETIME,'01' + @dt, 112) as [date], I still get the same error

  • December 9, 2013 at 1:58 am

    #1671825

    OK, fine, but what I said still stands. Did you try what I suggested?

    By the way, there are much better ways of getting the first day of the month than string manipulations. You can do it with some fairly simple date arithmetic. If you search for something like "t-sql get first day of month" then you should learn how to do it quite quickly.

    John

    Edit - corrected typo

  • December 9, 2013 at 6:00 am

    #1671904

    This query works fine:

    DECLARE @dt CHAR(8)

    SET @dt = cast(MONTH(getdate()) as char(2)) + '01' + cast(year(getdate()) as char(4))

    SELECT CONVERT(datetime,RIGHT(@dt,4)+LEFT(@dt,2)+SUBSTRING(@dt,3,2),112)

    ___________________________________
    Computer Enterprise Masoud Keshavarz
    I don't care about hell.
    If I go there I've played enough Diablo to know how to fight my way out.

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

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