Hello and thank you ahead for the assistance.
I'm at a loss now in how to convert what seems to be a set of raw dates that are in multiple formats from a SQL database., they are unfortunately organised in multiple ways:
yyyy-mm-dd (e.g 2023-01-18)
mmm dd yyyy h:mm AM/PM (e.g Oct 24 2022 4:13PM)
yyyy-mm-dd hh:mm:ss.00000000 (e.g 2021-11-30 00:00:00.0000000)
dd/mm/yyyy (e.g 05/07/2010)
I've tried to convert them on the SQL side using
COALESCE(TRY_CONVERT(DATE, [Date Raw], 101), TRY_CONVERT(DATE, [Date Raw], 103),TRY_CONVERT(DATE, [Date Raw], 23)) which for the most part works (but errors deciphering between UK and US formats)
I've tried on Excel side (wanting to utilise both Excel and Power BI)
using INT, DATEVALUE(TEXT), excelforum.com/excel-general/1133100-changing-a-date-format-that-includes-time-mmm-dd-yyyy-h-mm.html,
And other options that are lost in my search history. They seem to work if there is a consistent format, but as I am stuck with multiple like the above, there's always a hurdle it gets stuck at.
Is it possible to convert these into a useable format at all?
I've attached a workbook with the examples of dates listed with the raw dates, SQL converted dates and date matched column to show where SQL is failing. Examples should include all styles listed above (Original File Size is too large)
Truly thank you! It's mind boggling and I'm at a loss now.
Bookmarks