SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert Varchar to date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

evanburen
Posting Yak Master

155 Posts

Posted - 03/03/2013 :  09:40:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 03/03/2013 :  11:15:37  Show Profile  Reply with Quote
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

USA
789 Posts

Posted - 03/03/2013 :  11:26:33  Show Profile  Reply with Quote
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

155 Posts

Posted - 03/03/2013 :  11:49:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 03/03/2013 :  12:01:03  Show Profile  Reply with Quote
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

155 Posts

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

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 03/03/2013 :  15:06:17  Show Profile  Reply with Quote
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

155 Posts

Posted - 03/03/2013 :  15:54:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 03/03/2013 :  16:46:32  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000