Post reply

Error - The conversion of a varchar data type to a datetime data

  • September 27, 2020 at 10:25 am

    #3791149

    Hi

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

    Select STUFF((Select Distinct + ', '+ A1.date

    from Test A1

    where A1.Entry in (Select distinct bref from test3 where dentry = 1221)

    FOR XML PATH('')),1,1,'') as OrderDate

    from Test0 T0

    Month should be 3 alphabet characters.

    Date should be displayed as 23-Sep-2020

    Thanks

    • This topic was modified 4 years, 2 months ago by  jsshivalik.

  • September 27, 2020 at 3:19 pm

    #3791161

    you need to provide a lot more detail - as you aren't doing any explicit conversion I assume that either A1.Entry is a date/datetime or test3.bref is - and the other isn't.

    or eventually dentry is a date/datetime.

    or the error is from  ', '+ A1.date - in this case you need to convert the date to a varchar - see note below regarding convert/cast

     

    regardless you need to supply create tables statements, and contents of the fields involved.

     

    regarding displaying date on that format that is up to the application reading the data to do - not SQL. However in some cases you can convert a date to a string - search documentation for convert/cast for the info on it and pick the one that matches your desire output.

  • September 28, 2020 at 7:29 am

    #3791230

    The fix is:

    Select STUFF((Select Distinct + ', '+ CONVERT(varchar(20), A1.date, 105)

    ...

    Since A1.date is a datetime, which has a higher precedence than varchar, SQL is attempting to add ',' to the datetime.  Needless to say, that can't work.

    Here's an example of where the add will work:

    select getdate() + 0.75

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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