-1

I have a problem converting a SQL Column (date_column) to 104 date format. In the date column are two different types of dates. One is in dd-mm-yyyy format and the other one is a consecutive number format (39448). I wrote this query to convert the date into 104 date format (yyyy-mm-dd), but it didn't work.

SELECT date_column
CASE WHEN [date_column] like '%-%'
     THEN try_convert(date,date_column,104)
     ELSE convert(date,cast(date_column - 2 as smalldatetime),104) 
     END
FROM xyz;

Error: Conversion failed when converting the nvarchar value '08-09-2017' to data type int.

James Z
  • 12,209
  • 10
  • 24
  • 44
DataAnalystAT
  • 1
  • So `date_column` is a VARCHAR() or NVARCHAR() type I assume, and not a proper DATE data type? – HardCode Oct 04 '21 at 18:12
  • 2
    For best results, store them in a real date column in the first place. It's **SO MUCH BETTER** to do the conversion once as you ingest the dates (or once to fix the table -- because it really is _**broken**_ if you're using varchar for this-- once and for all) rather than many times on the fly as you need to query it. You also get the ability to have meaningful indexes and built-in date operations as a bonus, and the storage is more efficient, too. – Joel Coehoorn Oct 04 '21 at 18:12
  • 2
    *"One is in dd-mm-yyyy format and the other one is a consecutive number format (39448)"* that there is your problem. You need to fix your design. That is the only solution here. The fact that you are mixing the types of ways you store the dates in the *same* column compounds the problem further, and only solidifies it needs fixing. – Thom A Oct 04 '21 at 18:16
  • Please don't use [ASAP](https://meta.stackoverflow.com/questions/326569/under-what-circumstances-may-i-add-urgent-or-other-similar-phrases-to-my-quest) in your question. – Dale K Oct 04 '21 at 18:59
  • `date_column - 2` looks to be the root of your problem. I suggest you first do `TRY_CAST(date_column AS int)` before doing any arithmetic. Note that `smalldatetime` to `datetime` conversion does not require a 104 `style` number – Charlieface Oct 04 '21 at 20:11

1 Answers1

0

The problem might be the "try_convert". The following fiddle worked for me. The one issue that I see though, is when you are converting from string to datetime (or smalldatetime), that doesn't specify the input format and in some cases, SQL Server may mix up the month and day.

Fiddle

CMGeek
  • 136
  • 5