not necessarily. it could be null or it could say kitty cat try_convert will return NULL.
Basically, we had a list of companies who we need to maintain insurance for. Anyone with nothing in that date field isn't relevant to the exercise. I've not set this up, I just have to try work with it.
The numbers stack up. 667 Companies we need to report on are being returned. The others will always be null.
Probably not - if the value is NULL the convert would return a NULL value instead of failing. The fact that you are getting a failure indicates an invalid value exists and is trying to be converted.
If you run the code on your table, filtering based on the TRY_CONVERT and include the original column - you will see invalid values.
read documentation on TRY_CONVERT. The issue might be copy pasta of other people's albeit very nice code
OK, I'll revisit on Monday
The concern I have is that you don't know (for sure) that every string is being converted correctly until you have validated that those items that have not been converted are not some other format.
You're both absolutely right.
I've just run a quick select on the table with that column and found 11 rows with junk in them.
Junk dates that is
now you have 11 earlier it was 15K....worrisome
Just to confirm - you have over 15K with nothing (NULL) in that column and 11 with bad data and approximately 667 with date values that can be converted. After reviewing the data you have determined that those entries with NULL are not a concern and should not be included - but those 11 are still questionable.
If you are satisfied that those 667 that are being converted are in the correct format for conversion - and are converting correctly, and those additional 11 that are not able to be converted should not be included then the solution I provided will work - at least until someone enters a valid date string in a different format.
Question is, how will we ever know the data is being input correctly without checking. Doesn't look like (validation is in place other than making sure it's a date).
I need to go back to the data inputter about the 11
Is it free hand entry or is the user being provided a calendar? Also make sure the date column is actually date type . You as the dba should be a bit more paranoid and make sure you do validation by good design and or constraints
They're given a calendar to select.
I'm not the dba, I've been asked to look at ways to get the data out of it for reporting purposes.
Touch base with the dba and have dba fix these issues. You will be getting wrong results in your report. Come audit time, someone might be looking for a new job
Yep, Already done, last Friday 
They're on with it!
Correct. That is the case!
Selet * from unknowntable
Where isdate(ELIExpiry) =0
How many rows you get for this
Just checking for ISDATE isn't going to be enough - if the application allows free text and just one user decides to enter MM/DD/YYYY or YYYY-MM-DD (or any other format other than DD/MM/YYYY) then it will be a 'valid' date. It may even be able to be converted using style 103 - but would be an incorrect value.
At this point - the assumption has to be that all strings formatted as NN/NN/NNNN are in fact DD/MM/YYYY because there is NO way to determine anything else. This is the danger of user strings as dates - you can never be sure what the user was entering.
You cannot know the data is being input correctly - there is no validation. How are you sure there is a check to validate the data is actually a date? Nothing in the database tells us that...it would have to be at the application level and obviously that isn't correct if you have non-date strings.
You can safely accept DD/MM/YYYY, YYYYMMDD and YYYY-MM-DD. You can use ISDATE() and CAST to do that, just don't explicitly specify format 103. If the local date setting is DD/MM/YYYY, SQL will interpret the date that way automatically.
SELECT ..., ELIExpiry_date, ...
FROM ...
CROSS APPLY (
SELECT CASE WHEN ISDATE(ELIExpiry) = 0 THEN NULL
ELSE CAST(ELIExpiry AS date) END AS ELIExpiry_date
) AS set_ELIExpiry_date
WHERE ELIExpiry_date IS NOT NULL --optionalThanks for all your replies. There's clearly work to be done before I can trust the data. I'll report back to the people who want this reporting on.