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.
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:
Post a Comment