Converting string to datetime with invalid dates

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

Converting string to datetime with invalid dates

Author

Topic

casso
Starting Member

3 Posts

Posted - 2011-05-13 : 06:33:09

Hi,

I'm running a query on a table where a user can enter a date as a string on the front end (the column datatype is varchar and I can't change this)

I need to be able to return the results ordered by this date so I need to check that the string is a valid date then convert it to a datetime type.

I've tried this and it returned and error.

Select content_Id, content.content_title,taxonomy_item_id, CONVERT (datetime, last_edit_comment, 103 )last_edit_comment
From taxonomy_item_tbl Inner Join content On content.content_id = taxonomy_item_tbl.taxonomy_item_id
WHERE taxonomy_id= 60841 AND (ISDATE(last_edit_comment) = 1 OR last_edit_comment LIKE '')
Order By last_edit_comment DESC

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

---

Then tried this but it returned no results.

Select content_Id, content.content_title,taxonomy_item_id, CONVERT (datetime, last_edit_comment, 103 )last_edit_comment
From taxonomy_item_tbl Inner Join content On content.content_id = taxonomy_item_tbl.taxonomy_item_id
WHERE taxonomy_id= 60841 AND ((ISDATE(last_edit_comment) = 1 AND last_edit_comment > '19950101') OR last_edit_comment LIKE '')
Order By last_edit_comment DESC

---

When I run the query with no conversion or checks

Select content_Id, content.content_title,taxonomy_item_id, last_edit_comment
From taxonomy_item_tbl Inner Join content On content.content_id = taxonomy_item_tbl.taxonomy_item_id
WHERE taxonomy_id= 60841
Order By last_edit_comment DESC

I get the results

10325 DEF Test 2 10325 28/01/2011
10328 XYZ Test 5 10328 24/12/2010
10332 Date Time Test 3 10332 06/05/2012 15.01
10330 Date Time Test 1 10330 04/05/2011 11:30
10331 Date Time Test 2 10331 04/05/11 11:31
10327 ABC Test 3 10327 02/01/2011 15:05
10324 ABC Test 1 10324 02/01/2009 15:00

Any help on this would be great, thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-13 : 07:50:49

I think the problem is that the optimizer is doing the CONVERT before the WHERE clause is reducing the superset.
My approach would be to change the part of the SELECT list to:
CASE
WHEN ISDATE(last_edit_comment)=1 THEN CONVERT(datetime,last_edit_comment,103)
END as last_edit_comment

No, you're never too old to Yak'n'Roll if you're too young to die.

casso
Starting Member

3 Posts

Posted - 2011-05-13 : 09:06:02

I'll give it a try now, thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-16 : 09:16:51

Also check for the length

CASE
WHEN ISDATE(last_edit_comment)=1 and len(last_edit_comment)=10 THEN CONVERT(datetime,last_edit_comment,103)
END as last_edit_comment

Also refer http://beyondrelational.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

casso
Starting Member

3 Posts

Posted - 2011-05-20 : 11:46:50

That worked, thanks very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources