September 23, 2005 at 1:11 pm
I have a query that has the following syntax......
select * from openquery (testserver,'select location,zip,convert(datetime,updated) from tstdbase.dbo.zipcodes where updated between convert(char(8),dateadd(year,-2,getdate()),112) and convert(char(8),getdate(),112)) a
The problem is that the updated column that I need returned converted to datetime is a varchar(8) field with the following syntax for dates......YYYYMMDD. That would be fine except the column has some records that have a blank value instead of a date. These blank values cause me to get the out-of-range datetime conversion error. I tried the following and received the same error.
select * from openquery (testserver,'select location,zip,convert(datetime,updated) from (select * from tstdbase.dbo.zipcodes where isdate(updated) = 1) z where updated between convert(char(8),dateadd(year,-2,getdate()),112) and convert(char(8),getdate(),112)) a
Does anyone know how I can exclude those values and still convert the field? I can't update the bad values, and I don't want to have to create a temp table to store all valid records in and then use that.
Any suggestions would be greatly appreciated,
Adam
September 23, 2005 at 1:20 pm
you could use the isdate() function:
Select * from table where isdate(column) = 1
September 23, 2005 at 1:27 pm
I have already tried that, it was what was different between my first syntax example and my second syntax example. I also have tried putting the isdate() function in the final where statement like so....
select * from openquery (testserver,'select location,zip,convert(datetime,updated) from tstdbase.dbo.zipcodes where isdate(updated) = 1 and updated between convert(char(8),dateadd(year,-2,getdate()),112) and convert(char(8),getdate(),112)) a
No matter where I include it, I still receive the error. I believe it is because of the datetime functions included in my where statement. The only fix may be to do a subtraction instead of using dateadd(). Like so, to decrease the beginning date 2 years, use updated - 10000. It isn't pretty but I think it is the only fix.
Adam
September 23, 2005 at 1:39 pm
Look at the COALESCE function for the conversion part. basically if the DATE field is NULL or EMPTY you could provide a dummy date to return
OR you could use a CASE WHEN statement....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply