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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Out-of-date range value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jayram
Starting Member

44 Posts

Posted - 02/24/2014 :  16:51:56  Show Profile  Reply with Quote
i am trying to find the some bad data which is not properly formatted for date columns

the column is Varchar(8) and has value like '06011984'. i know how to convert it into a datetime but there are some bad dates in there. how do i find the bad dates

i.e.: bad date '04100213' instead of '04102013'

i would use
CONVERT(datetime,RIGHT(DATE,4)+LEFT(DATE,2)+SUBSTRING(DATE,3,2))



i am using a query like this but it lists me all the records in the table, even the good ones

select * from table where date not like '[0-1][0-9][0-3][0-9][19-20][0-1][0-9]'

TG
Flowing Fount of Yak Knowledge

USA
5924 Posts

Posted - 02/24/2014 :  16:59:03  Show Profile  Reply with Quote
try this:
where isdate( stuff(stuff([date],5,0,'-'),3,0,'-')) = 0


Be One with the Optimizer
TG
Go to Top of Page

jayram
Starting Member

44 Posts

Posted - 02/25/2014 :  09:29:21  Show Profile  Reply with Quote
Thanks TG. Your code does what i need.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 02/25/2014 :  11:48:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
ISDATE(RIGHT(Col1, 4) + LEFT(Col1, 4))



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
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.05 seconds. Powered By: Snitz Forums 2000