August 5, 2011 at 9:01 am
I have an electronic medical records database that stores a lot of dates in a varchar field in the format YYYYMMDD. I sometimes need to combine that date with a datetime value from another table to create a single recordset. So I have a view that looks something like this:
select person_id, create_timestamp as 'tb_date'
FROM order_ WHERE actcode='86580'
UNION ALL
select person_id, convert(datetime, tb_dategiven) as 'tb_date'
from master_imm_tbskin_ where tb_dategiven is not null AND ISDATE(tb_dategiven) = 1
My view is called tb_tests, create_timestamp is a datetime data type and tb_dategiven is a varchar(8).
When I run the following:
SELECT * FROM tb_tests
I get 13,000 rows returned correctly, with the person_id and the date returned in DATETIME format.
When I run the following:
SELECT * from tb_tests WHERE tb_date > '8/1/2010'
I get back "Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
If I query the table like this:
SELECT * from tb_tests WHERE Isdate(tb_date) <> 1
I get no rows back.
This is driving me crazy. I run into this issue all the time on this database since there are so many of those varchar dates, but I can never seem to figure out what to do.
August 5, 2011 at 9:22 am
that kind of issue is tough...anytime you have dates in a non-datetime field, you would ideally want to change the datatype to fix that in the future. otherwise you get problems like this one today.
If you cannot do that, you'll want to do one of two things:
1. compare string to string:
if your data is YYMMDD, you should be able to find data like this:
SELECT * from tb_tests WHERE tb_date > '20100801'
if you want to compare string to date, you'll need a case statement to resolve the comparison:
SELECT * from tb_tests
WHERE
CASE
WHEN isdate(tb_date) = 1
THEN convert(datetime,tb_date)
ELSE NULL
END > '8/1/2010'
that will end up being a table scan, and will be slower. the first example might still be a table scan if you do not have an index on tb_date.
Lowell
August 5, 2011 at 9:50 am
I've tried both of those approaches and either way I still get the same error. Haven't I already done the conversion to a DATETIME in the view definition? So when I select * from my view I get back a datetime value (see attached).
I'm also already checking for invalid dates within the view definition.
In addition - when I run the following:
SELECT person_id, tb_date, isdate(tb_date) FROM tb_tests
All 13,000 records come back with isdate(tb_date) = 1.
August 5, 2011 at 10:03 am
P.S. I WISH I could change the db to not store dates as varchar, but it's a 3rd party app and that's just the way they designed it
However, I did resolve the issue using your case statement within my view definition. So now it looks like this:
select person_id, create_timestamp as 'tb_date'
FROM order_ WHERE actcode='86580'
UNION ALL
select person_id,
CASE WHEN (tb_dategiven) = 1 THEN convert(datetime, tb_dategiven)
ELSE NULL END as 'tb_date'
from master_imm_tbskin_ where tb_dategiven is not null AND ISDATE(tb_dategiven) = 1
So now I can get on with my life. Thank you very much for the response, I definitely needed a 2nd set of eyes. 🙂
August 5, 2011 at 10:16 am
I have a similar problem in our ERP app.
One way I was able to fix it (because all the dates were actually real dates) was to do
SET DATEFORMAT YMD and then 100% of the rows returned ISDATE() = 1.
Maybe you can try a couple variations to see if this can work for you (MDY, DMY ...)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply