Convert field but skip bad records

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 2008 Forums

Transact-SQL (2008)

Convert field but skip bad records

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-07-30 : 00:17:57

is there any way to do something like:

select * from mytable where convert(date,date_time)>=getdate()-180

any of the records that are not able to be converted, i just want to skip. i know it's messy, but we're not concerned about the bad stuff since alot of this stuff was form-based type-o type errors... we'd just discard them any way.

Thanks!

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-07-31 : 01:47:49

You can try isdate function to identify the offending rows like:

declare @s table (pk int identity(1,1) primary key clustered, YourDate varchar(50))
insert into @s (YourDate)
	select cast(getdate() as varchar(50)) union all
	select cast(getdate()-1 as varchar(50)) union all
	select 'yak'
	


select *
from	@s s
join	(	select pk
		from @s
		where isdate(YourDate) = 1
	) d on
	s.pk = d.pk
where	cast(YourDate as datetime) >= getdate()-180

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2009-08-25 : 01:08:54

just finally got to testing this, i've been crazy busy, but OMFG this is PERFECT!

Thanks!

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-08-27 : 23:37:32

Glad it helped.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources