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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy