sql server programming:
The convert() function fails because some of the rows have data that will not convert to datetime. I suggest that instead of 'invalid_date_text' you set the value to NULL. -- Arnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous [quoted text, click to view] "Ben Rum" <bundyrum75@yahoo.com> wrote in message
news:2pxvg.45491$OT.40937@newsfe6-win.ntli.net...
>I am importing text files into a staging table, then doing a number of
>checks on the data to flag potential erroneous rows.
>
> I am getting stuck when trying to check if Date_A is a later date then
> Date_B, which business rules don't allow. However, if some of the other
> rows contain data which is not a vliad date, the whole check fails - even
> if I unclude a where clause to ignore these rows...
>
> I've tried moving the where columns around, but no luck.
>
> Any advice appreciated.
>
>
>
> create table #Temp (
> row_id int null ,
> date_a varchar(30) null ,
> date_b varchar(30) null ,
> error_flag bit null default 0 )
>
> insert #temp ( row_id, date_a, date_b ) select 1, '12-jul-2006',
> '19-jul-2006'
> insert #temp ( row_id, date_a, date_b ) select 2, '10-jul-2006',
> '19-jul-2006'
> insert #temp ( row_id, date_a, date_b ) select 3, '20-jul-2006',
> '19-jul-2006'
> insert #temp ( row_id, date_a, date_b ) select 4, 'invalid_date_text',
> 'more_invalid_text'
>
> -- I want to flag row_id 3 as error_flag = 1
>
> update #temp
> set error_flag = 1
> where convert(datetime, date_a) > convert(datetime, date_b)
> where IsDate(date_a) = 1
> and IsDate(date_b) = 1
>
> I also tried:
>
> update #temp
> set error_flag = 1
> where convert(datetime, date_a) > convert(datetime, date_b)
> and row_id in ( select row_id from #temp where isdate(date_a) = 1 and
> isdate(date_b) = 1)
>
>
I am importing text files into a staging table, then doing a number of
checks on the data to flag potential erroneous rows.
I am getting stuck when trying to check if Date_A is a later date then
Date_B, which business rules don't allow. However, if some of the other rows
contain data which is not a vliad date, the whole check fails - even if I
unclude a where clause to ignore these rows...
I've tried moving the where columns around, but no luck.
Any advice appreciated.
create table #Temp (
row_id int null ,
date_a varchar(30) null ,
date_b varchar(30) null ,
error_flag bit null default 0 )
insert #temp ( row_id, date_a, date_b ) select 1, '12-jul-2006',
'19-jul-2006'
insert #temp ( row_id, date_a, date_b ) select 2, '10-jul-2006',
'19-jul-2006'
insert #temp ( row_id, date_a, date_b ) select 3, '20-jul-2006',
'19-jul-2006'
insert #temp ( row_id, date_a, date_b ) select 4, 'invalid_date_text',
'more_invalid_text'
-- I want to flag row_id 3 as error_flag = 1
update #temp
set error_flag = 1
where convert(datetime, date_a) > convert(datetime, date_b)
where IsDate(date_a) = 1
and IsDate(date_b) = 1
I also tried:
update #temp
set error_flag = 1
where convert(datetime, date_a) > convert(datetime, date_b)
and row_id in ( select row_id from #temp where isdate(date_a) = 1 and
isdate(date_b) = 1)
Ben Rum (bundyrum75@yahoo.com) writes: [quoted text, click to view] > I am getting stuck when trying to check if Date_A is a later date then
> Date_B, which business rules don't allow. However, if some of the other
> rows contain data which is not a vliad date, the whole check fails -
> even if I unclude a where clause to ignore these rows...
>
> I've tried moving the where columns around, but no luck.
> update #temp
> set error_flag = 1
> where convert(datetime, date_a) > convert(datetime, date_b)
> where IsDate(date_a) = 1
> and IsDate(date_b) = 1
UPDATE #temp SET error_flag = 1 WHERE Isdate(date_a) = 0 OR isdate(date_b) = 0 UPDATE #temp SET error_flag = 1 WHERE CASE error_flag = 0 THEN convert(datetime, date_a) END > CASE error_flag = 0 THEN convert(datetime, date_b) END A CASE expression is the only safe way to avoid the unwanted evaluations of expressions occur. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
You can also use the Case it in the same Update statement like this: update #temp set error_flag = 1 where Case when IsDate(date_a) = 1 then convert(datetime, date_a) else convert(datetime,'31 dec 9999') End [quoted text, click to view] >
Case when IsDate(date_b) = 1 then convert(datetime, date_b) else convert(datetime,'1 jan 1900') End This will also treat the case when one of the 2 dates are invalid and not just the 2 dates are invalid at the same time. I think this is the more efficient way to do that job. Hope it helps Steve [quoted text, click to view] "Erland Sommarskog" wrote: > Ben Rum (bundyrum75@yahoo.com) writes: > > I am getting stuck when trying to check if Date_A is a later date then > > Date_B, which business rules don't allow. However, if some of the other > > rows contain data which is not a vliad date, the whole check fails - > > even if I unclude a where clause to ignore these rows... > > > > I've tried moving the where columns around, but no luck. > > > update #temp > > set error_flag = 1 > > where convert(datetime, date_a) > convert(datetime, date_b) > > where IsDate(date_a) = 1 > > and IsDate(date_b) = 1 > > UPDATE #temp > SET error_flag = 1 > WHERE Isdate(date_a) = 0 > OR isdate(date_b) = 0 > > > UPDATE #temp > SET error_flag = 1 > WHERE CASE error_flag = 0 THEN convert(datetime, date_a) END > > CASE error_flag = 0 THEN convert(datetime, date_b) END > > A CASE expression is the only safe way to avoid the unwanted evaluations > of expressions occur. > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Don't see what you're looking for? Try a search.
|
