0

How do I convert the values of a varchar(25) column from dates in the format "JAN02/19" to "2019-01-02"(YYYY-MM-DD)?

Dale K
  • 25,246
  • 15
  • 42
  • 71
swathi
  • 91
  • 1
  • 9
  • 7
    Every time you think you've seen every wretched date format people can think of... there's a new one. :-P `SELECT CONVERT(DATE, SUBSTRING('JAN02/19', 4, 2) + STUFF('JAN02/19', 4, 3, ''), 6)`. And keep that as a `DATE` (or `DATETIME`) if you can, do *not* try formatting that back to a string in the database again -- that should be the responsibility of the client. – Jeroen Mostert Jan 15 '20 at 16:37
  • 5
    I would strongly recommend changing the type of the column to `datetime` rather then storing dates as strings. Then you cal let whatever is _displaying_ the dates choose the format. – D Stanley Jan 15 '20 at 16:39
  • Why do you assume that **string** refers to 2019? Isaac Asimov was born in 1920 after all. You have a very serious problem, for which there's no excuse. You should change that field to `date` or `datetime2` as fast as possible – Panagiotis Kanavos Jan 15 '20 at 16:43
  • @JeroenMostert it worked, thank you for your suggestion on not formatting back to string. – swathi Jan 15 '20 at 16:44
  • @DStanley thank you for your suggestion for changing the column datatype to date. – swathi Jan 15 '20 at 16:44
  • Before converting you should verify what those strings refer to, to ensure you don't change the century by mistake – Panagiotis Kanavos Jan 15 '20 at 16:44
  • Just in case anyone thinks two-digit years aren't that bad, several UK banks' including [Lloyd's systems failed twice because of this already](https://www.theregister.co.uk/2020/01/02/lloyds_outage/). Also [reported here](https://www.standard.co.uk/news/uk/lloyds-halifax-bank-of-scotland-online-banking-crash-a4323976.html) – Panagiotis Kanavos Jan 15 '20 at 16:48
  • @PanagiotisKanavos At the bank, we spent tens of millions of dollars on Y2K. I was amazed at how deep and pervasive the two digit year was. – John Cappelletti Jan 15 '20 at 16:52
  • @JohnCappelletti and yet, one of the biggest banks in the world fails in 2020 due to a hacky fix... – Panagiotis Kanavos Jan 15 '20 at 17:01

2 Answers2

2

Perhaps something like this

Declare @S varchar(25)='JAN02/19'

Select try_convert(date,replace(@S,'/',' 20'))

Returns

2019-01-02
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

You can do something like below:

DECLARE @Date Varchar(10)
Set @Date='JAN02/19'
select DATEFROMPARTS('20'+substring(@Date,7,2),CHARINDEX(@Date,'JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC')/4+1,substring(@Date,4,2))

This should give the output in the desired format.

Edit:

To accomodate improper month values:

DECLARE @Date Varchar(10)
Set @Date='JAN02/19'
select DATEFROMPARTS('20'+substring(@Date,7,2),NULLIF(CHARINDEX(substring(@Date,1,3),'JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC'),0)/4+1,substring(@Date,4,2))

This will return NULL in case the month values are not proper.

CR7SMS
  • 2,520
  • 1
  • 5
  • 13
  • A clear approach -- my only niggle would be that, for an invalid month name, this returns a date with the month set to January rather than complaining. – Jeroen Mostert Jan 15 '20 at 16:46
  • Well now it's just clunky. :-) I suggest using `NULLIF(CHARINDEX(substring(@Date,1,3),'JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC'), 0)` to avoid repeating the expression. `NULLIF` and null propagation are nifty. – Jeroen Mostert Jan 15 '20 at 16:56
  • Edited the answer to use NULLIF... Thanks for the suggestion @Jeroen – CR7SMS Jan 15 '20 at 17:02