Hello list maker gurus,
We are trying to make what I thought was a simple list to gather constituents within an age range.
The only criteria being the Attribute 'Birthdate_1', the operator 'Between', and the values '01/01/1992' And '01/01/2012'.
To my eyes, that should work, but on generating the List, we always have an error returned 'SQSTATE =22007, Microsoft OLE DB Provider for SQL Server The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.'
I am sure this must be a common type of List to generate, are we missing something obvious?
Cheers,
Nicholas
Hi Nicholas
Sounds to me like you might have some bad data in your TX_CUST_KEYWORD table. Although you define your attributes as dates or whatever, they're actually all stored as text, so if you get bad data in there through an import error or other backend change, SQL Server will happily allow that value to stay in there.
Dates should be stored internally as proper iso-format date strings ('YYYY-MM-DD'), if Tess has put them in.
If there are any values for date-formatted attributes that don't match that format, or can't be implicitly converted to it, then SQL code won't convert them correctly.
So I'd run a query something like this below, and any bad values should pop out. Our usual experience is that an import has put the date in there as DD/MM/YYYY, which SQL won't interpret properly, because it thinks the natural order is MDY.
----------------------------------------------------
select ck.customer_no , ck.keyword_no , k.description , ck.key_value , ISDATE(ck.key_value) chk
from TX_CUST_KEYWORD ck
join T_KEYWORD k on ck.keyword_no = k.keyword_no
where ck.keyword_no in ( select keyword_no from T_KEYWORD where data_type = 3)
AND ISDATE(ck.key_value) =0
order by ck.key_value
-------------------------------------------------------
Hi Ken,
Thank you very much for sharing that query. We did have some bad dates earlier which I thought I had cleaned up (using a much less sophisticated and more time consuming method than yours...).
Perhaps there are still some stragglers that need working on. I will post back after some further cleaning.
Cheers!