@Scott - correct, but you cannot confirm that what is entered by the users is DD/MM/YYYY or any other format. Since this is apparently a free text field - the users can enter the date in any format.
There is no way to confirm that the user who entered 10/09/2024 meant September 10th - or they meant October 9th. Which also means there is no way to determine if 2024-10-09 is October 9th or September 10th (which - for the datetime data type will be interpreted as YYYY-DD-MM and not YYYY-MM-DD).
If the user entered 2024-10-09 and they meant that date to be September 9th - and you convert the value to a date it will be converted to October 9th which would not be correct.
If the application developers can state - with certainty - that any string entered in NN/NN/NNNN format will always be DD/MM/YYYY and that a valid date will never be entered in any other format, that at least would give confidence in the conversion.
agreed. this was just for a quick vet
I see the problem now.
It does ask to select from a calendar though
I see the issue - the application provides the ability to allow the users to setup and used 'custom' fields. The problem with this approach is that the 'custom' field is going to be dependent on the users regional settings.
If the users regional settings are US English - you get the date in MM/DD/YYYY format. If the users regional settings are British English - you get the date in DD/MM/YYYY format.
The application team should have set that up to use a unambiguous date format (YYYY-MM-DD or YYYYMMDD) instead. That would avoid any issues with the format - but it doesn't seem to have been setup that way.
Now the question is how did you get 'junk' data into that custom column? Best guess is that when it was created it was original created as a text box (free text) - then changed to use a date-picker. But that is just a guess.
YYYYMMDD is the only unambiguous format. YYYY-NN-NN can be either MM-DD or DD-MM in SQL Server, depending on local date settings (no matter what the ANSI/ISO rules say, that is reality in SQL Server).
YYYY-MM-DD is unambiguous for the date/datetime2/datetimeoffset data types - but not for smalldatetime or datetime.
That is correct.
I've asked the data input to check that all their dates are correct, asked the developer to add validation, then the input to check again.
I've decided on a few reports but I understand the data does need checks against it.