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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert Varchar to date

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 Table1
WHERE
convert(datetime,stuff(stuff(DateofDeath,5,0,'/'),3,0,'/'),101) = '19840201'
If it is in the ddmmyyyy format, try this:
SELECT * FROM Table1
WHERE
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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

evanburen
Posting Yak Master

167 Posts

Posted - 2013-03-03 : 12:10:54
Actually, I'm getting the same error for both examples
Go to Top of Page

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 DeathFile
SET 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.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

evanburen
Posting Yak Master

167 Posts

Posted - 2013-03-03 : 15:54:49
Thanks, Jim.

Unfortunately, when I try

select DateofDeath from Table1 Where IsDate(DateofDeath) = 0

it returns every record in the table.

select DateofDeath from Table1 Where IsDate(DateofDeath) = 1

returns zero rows.

This is driving me crazy.
Go to Top of Page

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 table
ORDER BY 1,2



Everyday I learn something that somebody else already knew
Go to Top of Page
   

- Advertisement -