Search This Blog & Web

Monday, April 23, 2012

Returning Valid date values from nVarchar column without conversion error

I come across a problem that is common in our daily usage but a little tricky. We have a table and a column with thousands of records and there is a column nVarchar that stores date values in a specific format. But when i applied convert or cast statement to convert these values into date format it generate error. It means there are invalid values in this column. Now i need my result set with out these invalid values and i don't have enough time to find out error values and remove or replace them.

Following is the query i have used to find out valid values and store it in a temp table then query my table to get valid values and use it as comparison.

Declare @DailyReport table(ID int, dtm datetime,DateOfContact varchar(50))

Insert into @DailyReport values (1,getdate(),'23-04-2012')
Insert into @DailyReport values (2,getdate(),'24-04-2012')
Insert into @DailyReport values (3,getdate(),'21-04-2012')
Insert into @DailyReport values (4,getdate(),'test')
Insert into @DailyReport values (5,getdate(),'26-04-2012')

;With DOC
as
(
 Select id,
convert(datetime,dateofcontact,103) as DOC
 From @DailyReport
 where
 (isnumeric(substring(dateofcontact,0,2)) = 1
 and
  isnumeric(substring(dateofcontact,4,2)) = 1
 and
  isnumeric(substring(dateofcontact,7,4)) = 1
  )
 )
select * from doc

here is the code picture and result. i have used pre-conversion check to validate date and omit all invalid rows before conversion.

Date time conversion





No comments: