0

I have a column for which I need to convert the data to DATE format. I receive below error whenever I encounter the data 'MM/YYYY/DD' format, but it works fine for other formats.

Conversion failed when converting date and/or time from character string

Sample data:

SELECT CAST('05/2020/29' AS DATE);

SELECT ISDATE('05/2020/29') returns 1 but CAST or CONVERT functions fail.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mahendra Dhaker
  • 43
  • 8
  • Is this not a duplicate of [How to convert custom string to Date in SQL Server](https://stackoverflow.com/questions/46324500/how-to-convert-custom-string-to-date-in-sql-server) – Abra Sep 28 '20 at 09:09

3 Answers3

3

That's a weird format. I would recommend datefromparts():

select datefromparts(
    substring(mycol, 4, 4), 
    left(mycol, 2), 
    right(mycol, 2)
) mydate
from mytable

Demo on DB Fiddle:

with mytable as (select '05/2020/29' mycol)
select datefromparts(substring(mycol, 4, 4), left(mycol, 2), right(mycol, 2)) mydate
from mytable

| mydate     |
| :--------- |
| 2020-05-29 |
GMB
  • 216,147
  • 25
  • 84
  • 135
3

I must admit, the format MM/yyyy/dd is a rather odd choice of format for a date. The reason, problem, however, is that you're storing date (and time) data in a varchar.

To do this, however, I could rearrange the data into an ISO format (yyyyMMdd) and then CONVERT:

SELECT TRY_CONVERT(date,CONCAT(SUBSTRING(YourString,4,4),LEFT(YourString,2),RIGHT(YourString,2)),112)
FROM dbo.YourTable;

I use TRY_CONVERT here as experience has proved countless times that those that store date (and time) data as a varchar in a database also end up with bad data (like '13/2020/11' for your style). This means that the conversion won't fail when these bad rows are met and NULL is returned instead.

Ideally, though, you should be fixing your column and changing the data type. We can actually do this using a similar solution to the above. Firstly by changing the value to the ISO format in the column, but as a varchar, and testing the conversion, and then altering the data type:

UPDATE YT
SET YT.YourString = CONVERT(varchar(8),V.YourDate,112)
FROM dbo.YourTable YT
     CROSS APPLY (VALUES(TRY_CONVERT(date,CONCAT(SUBSTRING(YourString,4,4),LEFT(YourString,2),RIGHT(YourString,2)),112)))V(YourDate);

ALTER TABLE dbo.YourTable ALTER COLUMN YourString date NULL;

Side note as well, ISDATE is a terrible function, like it's "sibling" ISNUMERIC. Both functions return false positives and negatives, and should be avoided. TRY_CONVERT and TRY_CAST are better alternatives by far.

As a quick example, notice the following values returned for the below:

SET LANGUAGE ENGLISH; --American, not English...

SELECT ISDATE(20200928); --Returns 1
SELECT ISDATE(17); -Returns 0
SELECT ISDATE('09/2020/28'); --Returns 1

And now, let's try some conversions:

SELECT CONVERT(datetime,20200928); --ISDATE returned 1

Arithmetic overflow error converting expression to data type datetime.

SELECT CONVERT(datetime,17); --ISDATE returned 0

Success, returns the datetime 1900-01-18T00:00:00.000.

SELECT CONVERT(datetime2(0),'09/2020/28'); --ISDATE returned 1

Conversion failed when converting date and/or time from character string.

Thom A
  • 88,727
  • 11
  • 45
  • 75
2

You can try it specifying first the dateformat:

SET DATEFORMAT MYD;
SELECT CAST('05/2020/29' AS DATE);
Xavi Anguera
  • 105
  • 7