-1

Issue: COMPLETE_DATE column is of type varchar (it is loaded like this, I cannot change this), I need to convert it to a Numeric datatype for manipulation. Format of date is yyyy-mm-dd.

When I run the convert function, I get this error:

Error converting data type varchar to numeric

Solution attempt 1: I ran the LEN function and noticed I was getting 11 returned instead of the expected 10. Initially I thought it was perhaps extra spaces so I

CONVERT(NUMERIC, RTRIM(LTRIM(COMPLETE_DATE)))

but it still errors out.

Solution attempt 2:

CONVERT(numeric, REPLACE(LTRIM(RTRIM([COMPLETE_DATE])), ' ', ''))

I am running a script from my predecessor where converts this column using the convert function but I am not sure at this point it is any extra space, or maybe special characters hiding, what else can I do to cleanse the column to just the string yyyy-mm-dd?

UPDATE tbl SET ACADEMIC_YEAR = CASE WHEN CONVERT(NUMERIC,SUBSTRING(COMPLETE_DATE,1,4)+SUBSTRING(COMPLETE_DATE,6,2)+SUBSTRING(COMPLETE_DATE,9,2)) >= CONVERT(NUMERIC,SUBSTRING(COMPLETE_DATE,1,4)+'0701')

THEN CONVERT(NUMERIC,SUBSTRING(COMPLETE_DATE,1,4)) ELSE CONVERT(NUMERIC,SUBSTRING(COMPLETE_DATE,1,4))-1 END;

Hakka-4
  • 87
  • 8
  • 3
    Firstly, why are you storing a date valyue as a `varchar`, and secondly *why* are you converting it to a `numeric`? There are 6 date and time data types, and 5 of those are infinitely better choices (only `time` is the wrong choice). Use a `date`. – Thom A Jan 07 '21 at 16:51
  • 2
    What **version** of SQL Server are you using? The currently supported ones have `TRY_CONVERT` or `TRY_CAST` function that will take your input string and convert it to - preferably - a `DATE` or `DATETIME2(n)` and will just return `NULL` if the conversion doesn't work - instead of a hard error – marc_s Jan 07 '21 at 16:52
  • If you are going to `numeric` (quite questionable, as others have said), *what* number do you expect e.g. `2021-01-07` to be converted to? – Damien_The_Unbeliever Jan 07 '21 at 16:58
  • @Larnu I am not the DBA, that is how they loaded the data. I would never load it as a varchar precisely to avoid this error. – Hakka-4 Jan 07 '21 at 17:00
  • That doesn't explain why you then want to convert it to a `numeric`, @Hakka-4 . The *real* solution here is the fix the design though. – Thom A Jan 07 '21 at 17:06
  • @Larnu I just added the lines of code he was using. Maybe that helps with the intent. Essentially, he was taking this Varchar field of COMPLETE_DATE to a) get a Substring from it and B) to perform calculations. – Hakka-4 Jan 07 '21 at 17:08
  • @Hakka-4 dates have no format. They are binary values. They aren't numbers either. Using the wrong type to store dates is a serious design bug. You can't just cover it up by casting. You simply *can't* read a date as a number either, because there's **no** numeric literal format that matches a date. There's no sensible conversion from a date value to a number either. If the strings in that field all have the YYYY-MM-DD format you can use `cast(thatTextField as date)`. Performance will be terrible though, as the server won't be able to use any indexes on that text field – Panagiotis Kanavos Jan 07 '21 at 17:11
  • 1
    If all the data is in the format `yyyy-MM-dd` why not just do `ALTER TABLE dbo.tbl ALTER COLUMN ACADEMIC_YEAR date;`? That *literally* fixes all the problems. – Thom A Jan 07 '21 at 17:12
  • @Hakka-4 `and B) to perform calculations` fixing the bad type will allow you to perform calculations using date functions. You can't treat a date as a number. `CONVERT(NUMERIC` makes no sense – Panagiotis Kanavos Jan 07 '21 at 17:12
  • @Hakka-4 you wouldn't have to clean or convert anything if you used the correct type. It would be impossible to insert bad data into the table to begin with. Right now, you're trying to clean the data every time you perform the query, without even knowing what's wrong. If you don't want to fix the bug, you have to find out what's wrong at least. Use `TRY_CAST` to find which values fail and what they contain. You won't be fixing the real bug though, you'll only cover it up – Panagiotis Kanavos Jan 07 '21 at 17:17
  • Have you tried having a master WHERE condition that limits the rows you are trying to convert to only those with valid dates? Like WHERE IsDate(COMPLETE_DATE) = 1. You can also use this in reverse to find offending rows and systematically figure out why each one is not a valid date, and edit your UPDATE statement accordingly. Also, consider creating a calculated column that does the cleanup and use it for your other queries. No need to run updates ever again... – DanielG Jan 07 '21 at 17:25

2 Answers2

0

If the format is YYYY-MM-DD, then try:

try_convert(int, replace(academic_year, '-', ''))

I'm a little confused why you think that removing spaces will affect the conversion.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I found the only way to fix this issue, if you ever encounter it. It is not perfect, but it was an amalgamation of the responses answered by some of the users that replied. I went ahead and did a few things.

  1. Because I was unable to identify whether it was an extra space of hidden character I went ahead and did a TRY_CONVERT function to a data while simultaneously using the RTRIM AND LTRIM but as suggested instead of a NUMERIC type I went with a DATE datatype, however the key really was to SUBSTRING'ing the actual date of 10. So it looked like this

try_convert(date,rtrim(ltrim(SUBSTRING(COMPLETE_DATE, 1, 10)))) as COMPLETE_DATE

this allowed for whatever extra space or character to be expunged and only 10 chars remained, then that string of 10 was CONVERTED to a DATE and the issue was resolved.

Not sure if this is the best way to do it, but it certaintly worked for me.

Hakka-4
  • 87
  • 8
  • Aside: There is a [`trim`](https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver15) function. You could use `try_convert(date,trim(rtrim(ltrim(SUBSTRING((COMPLETE_DATE), (1), (10))))))` if you are paid by the pairs of parentheses. Or just `try_convert(date,trim(SUBSTRING(COMPLETE_DATE, 1, 10)))`. Unfortunately either one returns a `date`, not a `numeric`, so it doesn't answer _your_ question. You may also be interested in [trimming all whitespace from a string](https://stackoverflow.com/a/35247507/92546). – HABO Jan 07 '21 at 21:10