Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
evanburen
Posting Yak Master
167 Posts |
Posted - 2013-03-03 : 09:40:03
|
SELECT * FROM Table1 where CONVERT(DateTime, DateofDeath) = '02/01/1984'I'm trying to convert a field defined as varchar(8) to a date field but I'm getting the error "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value". The data for this field looks like this 02011984.I've looked at other posts on this forum and used ISDATE(DateofDeath) = 0 and LEN(DateofDeath) != 8 to root out the bad data. I'm not concerned about the end format of this field; I just want to be able to run queries against this field.Thanks |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-03 : 11:15:37
|
Try this, assuming the date is in the mmddyyyy format.SELECT * FROM Table1WHERE convert(datetime,stuff(stuff(DateofDeath,5,0,'/'),3,0,'/'),101) = '19840201' If it is in the ddmmyyyy format, try this:SELECT * FROM Table1WHERE convert(datetime,stuff(stuff(DateofDeath,5,0,'/'),3,0,'/'),103) = '19840102' If you always stored date in a column that is of type datetime, you would not run into this problem ever. If you must store as character strings, store it in YYYYMMDD format, which will always be interpreted correctly. |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-03-03 : 11:26:33
|
How about this...SELECT * FROM TABLE1 WHERE DateOfDeath = '02011984';This will search based on the varchar - with no conversions required. If you are passing in a date, use convert on the variable - as in:SELECT * FROM TABLE1 WHERE DateOfDeath = convert(char(8), @dateVariable, 121);By converting the column, you are eliminating the ability of SQL Server to use an index. Converting the variable to char/varchar an index can be used and you are comparing string to string. |
|
|
evanburen
Posting Yak Master
167 Posts |
Posted - 2013-03-03 : 11:49:19
|
I'm building this table from scratch and it is based on the Social Security Death file which contains almost 90 million records. It's a 12GB text file and the date of death format is like this '02011984'. If I try and import the text file into a new table defined as datetime, it always fails for some reason. Ideally, I would like to store this field as a datetime and not have to convert it all but importing it as a varchar is the only way I've been able to get it to work. Also, there are many invalid dates like this '02001984' where I assume they didn't know the exact date of death and just the month.James K - using your first example, I get the same 'conversion...out of range value' error. jeffw - I need to be able to do queries like WHERE DateofDeath > convert(char(8), @dateVariable, 121) but this doesn't work.Thanks for the help |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-03 : 12:01:03
|
quote: James K - using your first example, I get the same 'conversion...out of range value' error.
Then it may be that your data is in the ddmmyyyy format? |
|
|
evanburen
Posting Yak Master
167 Posts |
Posted - 2013-03-03 : 12:10:54
|
Actually, I'm getting the same error for both examples |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-03-03 : 15:06:17
|
You'll have to decide what to do with bad dates like 02001984. MM00YYYY will always fail to convert, as will 00MMYYYY, which, since this is a gov't file, probably exists in your data as well. Import the data as varchar, and then create a field(say DeathDate) that is of date datatype. Then update that field with your converted varchars.UPDATE DeathFileSET DeathDate = CASE WHEN isdate(DateOfDeath) = 1 THEN DateOfDeath ELSE NULL END. That'll tell you what your bad dates are, and you can then decide what to do with them.JimEveryday I learn something that somebody else already knew |
|
|
evanburen
Posting Yak Master
167 Posts |
Posted - 2013-03-03 : 15:54:49
|
Thanks, Jim.Unfortunately, when I tryselect DateofDeath from Table1 Where IsDate(DateofDeath) = 0it returns every record in the table.select DateofDeath from Table1 Where IsDate(DateofDeath) = 1returns zero rows. This is driving me crazy. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-03-03 : 16:46:32
|
According to your query, there are no valid dates in you table. May be ISDATE isn't as reliable as I thought? Just to get a grip of what types of values are in your data, let's just parse it. You should be able to eyeball potential the problem(s). Out-of-range errors means month 13 or Feb 31, etc., so they must be in there somewhere. Are you sure that all dates are in MMDDYYYY? If the gov't cobbled this together from state lists, the format may vary from record to record. I've dealt with govt files before, and this scenario isn't unplausible.SELECT DISTINCT CONVERT(tinyint,LEFT(DateOfDeath,2)) as SupposedMonth , CONVERT(tinyint,(SUBSTRING(DateOfDeath,3,2)) FROM your tableORDER BY 1,2Everyday I learn something that somebody else already knew |
|
|
|
|
|
|
|