Out-of-date range value - SQL Server Forums

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

jayram
Starting Member

47 Posts

Posted - 2014-02-25 : 09:29:21

Thanks TG. Your code does what i need.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources