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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Out-of-date range value

Author  Topic 

jayram
Starting Member

47 Posts

Posted - 2014-02-24 : 16:51:56
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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-24 : 16:59:03
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

47 Posts

Posted - 2014-02-25 : 09:29:21
Thanks TG. Your code does what i need.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-25 : 11:48:34
ISDATE(RIGHT(Col1, 4) + LEFT(Col1, 4))



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -