Hi. I've got a field that I'm trying to report on. It's set as a string in the database but formatted as short date.
When I try report it throws out this error
Conversion failed when converting the varchar value '10/09/2024 00:00:00' to data type int.
What I'm trying to do is return all records for the next 30 days but first I need to CONVERT to the correct allowed format and then run the next 30 days part.
I've had a look around but I'm going round in circles for the last 3 hours.
Any guidance appreciated 
Can you provide sample data and the code you are attempting to execute?
1 Like
Hmm, why is it converting to an "int"? Convert it to a date, then, if you need to, change the date to an int using DATEDIFF(DAY, ...).
1 Like
Also - is the date 10/09/2024 - October 9th or September 10th?
1 Like
I’ll try tomorrow. It’s difficult to get to the code as I’m working on a user interface and trying to build a report from there.
I think I’m going to have to look at it another way rather than through the user interface. I’ll try direct in sql server tomorrow.
The user interface has a feature to create sql expressions on the data but it appears some of that data is flaky.
I think you should know that the days are int and the time is .12345. So I think it should be enough to convert your date to a varchar(12) so only the date remains, if you have bad data you can also add the isnumeric function to be sure.
CONVERT(INT,
CASE
WHEN IsNumeric(CONVERT(VARCHAR(12), '10/09/2024 00:00:00')) = 1 THEN CONVERT(VARCHAR(12),'10/09/2024 00:00:00')
ELSE 0 END)
1 Like
I've got myself in a bit of a muddle tbh.
What we are trying to do is report on those companies whos insurance expires in the next 30 days or has already expired.
I've now tried simplifying the command to get to the bottom of why its not working by using the expression
I tried ELIExpiry > GetDate() which is when I get the error
What I ultimately want to do is ELIExpiry < GetDate() +30
Its the ELIExpiry date that I need to CONVERT I think or is it the GETDATE() or Both. Whichever way, its not liking the datatype.
In a muddle!
Since ELIExpiry is a string column pretending to be a date you could have junk data in that column
Try this and post back. There should be no muddle puddle or shuddle
Selet * from unknowntable
Where isdate(ELIExpiry) =0
1 Like
You actually have multiple problems - the fact that the column is stored as a string (varchar) and the format that is stored is regional specific (DD/MM/YYYY).
If the language on the server is US English - the format DD/MM/YYYY will fail to convert to a valid date. US English format is MM/DD/YYYY and 13/09/2021 (September 13, 2021) cannot be converted on a US English system.
To convert that string to a valid date - you need to specify the appropriate style. You can identify any dates that are invalid using the following:
SELECT ...
FROM yourtable
WHERE try_convert(datetime, yourdate, 103) IS NULL;
This will identify any rows where {yourdate} cannot be converted to a valid datetime data type where the format expected is DD/MM/YYYY.
Once you have identified the bad rows - then you can determine what needs to be done to fix those values.
If possible - you can then add a computed column to the table using TRY_CONVERT to return a date or datetime data type. Persist that computed column and add an index and then your queries become:
SELECT ...
FROM yourtable
WHERE computed_column < DATEADD(day, DATEDIFF(day, 0, GETDATE()) + 31, 0)
Using DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) resets the time portion to 00:00:00.000 of a datetime data type and the '+ 31' adds 31 days. This then gets you all dates and times less than 31 days from today - not including the 31st day from today.
If you are only working with dates - and not times - you can convert to a date data type and the above code will still work the same.
3 Likes
use sqlteam
go
create table donuts(id int, fakedate varchar(10))
insert into donuts
select 1, '13/09/2021' union
select 2, 'kitty cat'
SELECT *, try_convert(datetime, fakedate, 103)
FROM donuts
WHERE try_convert(datetime, fakedate, 103) IS NULL;
--where ISDATE(fakedate) = 0
drop table donuts
2 Likes
Thanks for your responses and asistance.
I love a challenge. We aren't interested in any record that has a NULL value in that date so that's my start point. Only 15000 of them causing the problem
you should be interested in those because they might have junk date data in that date column but might have important data in the rest of the columns. It is not that they have NULL dates, it is that they have junk data in a varchar column pretending to have date data. This check is not to find rows with NULL data
WHERE try_convert(datetime, fakedate, 103) IS NULL;
it is a check to see what junk data you have in that column
OK, with the help I've run the try convert and I get 15912 rows returned so its those that cant be converted.
can you show a sample of them? so eventually you are going to have to fix these issues
- clean things up
- convert that column to proper date time column
- change the application that runs on top of it to use proper data type
Hi
I created a computed column column using the TRY_CONVERT and it is now running as expected.
Thank you for all your help
you have just put a bandaid on the issue
notice what @jeffw8713 recommended
"Once you have identified the bad rows - then you can determine what needs to be done to fix those values."
also he said
"you can convert to a date data type and the above code will still work the same."
The question now is what is preventing those columns from being converted to a valid date? It would seem this column determines when a policy expires - but if it isn't a valid date then how does the system terminate that insurance policy?
You now have over 15,000 policies/companies that you cannot determine if they are expired, due to expire or will never expire - but instead of identifying them you are just ignoring them.
Worse - since you don't know what values are not able to be converted it is entirely possible that the dates are in MM/DD/YYYY format and not DD/MM/YYYY (as expected) - which means your convert is incorrect.
Just because you have something that doesn't generate an error - does not mean it is correct.
1 Like
There isn't junk in that column, just NULL. Isn't that why its failing? Because its trying to convert a NULL to a Date?