-2

I have a CTE table and I'm having this problem below

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

when I query piece of that particular code it's not working with CAST function, when I try TRY_CAST it's working, but it's not working in the main CTE select statement

I query varchar field and isdate function side by side, all of them returns 0
I checked all of them one by one and for example this one below it looks date data type to me 2019-06-01 00:00:00.0000000

I haven't seen any funky records like 019-06-01, I can't understand why I can't use it

Any ideas, solutions?

Thom A
  • 88,727
  • 11
  • 45
  • 75
brickanalyst
  • 13
  • 4
  • 2
    Why are you storing date (and time) data as a `varchar` in the first place? If you need to find what values are bad, however, then query your column with the following `WHERE`: `WHERE TRY_CONVERT(date,YourColumn) IS NULL AND YourColumn IS NOT NULL;` This will tell you what dates are bad. Then you'll need to take the time to *fix* that data. – Thom A Aug 16 '23 at 16:03
  • 1
    Please fellow the link below for this issue its already answered. https://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i – Fazal Qayyum Aug 16 '23 at 16:03
  • @ThomA because we have different records (integer,date,string) for different categories under one field that has to be kept in varchar. I tried your where clause and there was no records return. I even tried this CAST(REPLACE(LEFT(varchar_field,10),'-','') AS DATE) and it didn't work – brickanalyst Aug 16 '23 at 18:01
  • @FazalQayyum thank you, these records will be used in react application and javascript couldn't convert with T delimeter and that's why records are kept datetime2 without T delimeter and that's causing issue in ssms now. – brickanalyst Aug 16 '23 at 18:02
  • 2
    Sounds like you have a design flaw then, @brickanalyst . – Thom A Aug 16 '23 at 18:04
  • 3
    *"we have different records (integer,date,string) for different categories under one field that has to be kept in varchar. I tried your where clause and there was no records return"* these 2 statements conflict. One cannot be true if the other is. – Thom A Aug 16 '23 at 18:16

1 Answers1

0

while CAST function is not working and TRY_CAST is working, I added case when condition to return if values can be casted to date data type, else NULL

it's working.

ps: all records in the filtered select statement returns values 1 for ISDATE() I don't even know why CAST is not working.

brickanalyst
  • 13
  • 4
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 26 '23 at 23:15