-3

Varchar value "18-MAR-13" needs to be smartly converted to date as "18-03-2013" in DD-MM-YYYY format.

None of CAST, TRY_CAST, CONVERT, TRY_CONVERT worked.

I have tried with CONCAT, but it got too long.

BTurkeli
  • 91
  • 1
  • 2
  • 15
  • 3
    Is that DD-MMM-YY, or YY-MMM-DD? Which century? – jarlh Mar 05 '23 at 19:34
  • there is no smat way, converting all rows costs time and resources better spent elsewhere – nbk Mar 05 '23 at 19:42
  • Hi @BTurkeli, try to explictly string replace '-' to ' ' and then it is of the exact style #6 (dd mon yy), then do a explict convert to see whether it still throws exception. here is the query "select convert(date, replace('18-MAR-13','-',' '),6)", http://sqlfiddle.com/#!18/7a53a4/1 MS SQL all supported styles are listed here: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms187928(v=sql.90)?redirectedfrom=MSDN – Junjie Mar 05 '23 at 20:32

1 Answers1

3

Use convert with the appropriate style - assuming 13 means 2013, if not clarify in the question.

select Convert(varchar(10),Convert(date, '18-MAR-13'), 105)
Stu
  • 30,392
  • 6
  • 14
  • 33
  • Unfortunately, the column gives `Conversion failed when converting date and/or time from character string.` error. There are null values in the column. – BTurkeli Mar 05 '23 at 19:43
  • 1
    then chack for NULL and add inszead a value – nbk Mar 05 '23 at 19:49
  • 4
    @BTurkeli Then you have _bad data_ which is a direct result of storing a date as a string _in the first place_. Find it using `TRY_PARSE`: [example](https://dbfiddle.uk/C30SlCDh). If you're storing a date it should be stored as a date, period. That error is not caused by NULLs, it's caused by garbage. – Aaron Bertrand Mar 05 '23 at 20:11
  • I know, but thanks. – BTurkeli Mar 05 '23 at 20:54
  • @BTurkeli Not sure I follow - what do you know? Everything we've told you? So how can we help? What do you want SQL Server to do when you say "convert this column to a date" but it hits a row with garbage in that column? Do you want to ignore the row, make the value NULL, raise an error, ...? – Aaron Bertrand Mar 05 '23 at 21:17
  • TRY_PARSE works better. – BTurkeli Mar 31 '23 at 13:18