Date conversion error - 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 2008 Forums

Transact-SQL (2008)

Date conversion error

Author

Topic

sam4all
Starting Member

6 Posts

Posted - 2010-08-25 : 19:21:21

Hi T-SQL guru need help in understanding this sql.I am an oracle guy and this is my first encounter with sql server.

Column RelDate has date stored as varchar eg(2005 08 26,2005 0826 (wrong date)

There are 8000 records in the table(you will know why i am giving this info later in the post)

I need to filter out dates that are not valid an retrieve records with date less or equal to today.

I have a inner query to filter out invalid dates
using isdate
and covert the dates
using convert
which changes to date, great.
But when i try to use the result of the inner query to check with current date i get this error

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

select *
FROM (
select
convert(datetime,replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) REL_DATE
from record_dt
where len(reldate) > 4
and (isdate(replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) = 1)
) as INN
WHERE INN.REL_DATE < GETDATE()

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

I also tried using cast but still the same error.
This would work like a gem in oracle...without error...

But when i rewrite the query with top 10000 in inner query which retrieves all 8000 records

select *
FROM (
select top 10000
convert(datetime,replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) REL_DATE
from record_dt
where len(reldate) > 4
and (isdate(replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) = 1)
) as INN
WHERE INN.REL_DATE < GETDATE()

I am able to get the right result...

Sample Data
Use this sample data in a table you will know what i am talking about
2010 09 7
2010 0830
1999 01 26
1998 12 01
1999 03 09
2009 02 10
2008 10 28

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-25 : 23:19:59

It means you've got "bad" data in the table. You've got data that can't be converted to a date/time data type. And the "bad" data isn't in the top 10000 rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sam4all
Starting Member

6 Posts

Posted - 2010-08-26 : 14:13:28

I do not agree with your statment if you look into the query you will know what I am talking about
As I mentioned there are only 8000 records in the table ,The query has two parts
Inner Query filters invalid date with isdate function and converts the valid dates to datetime using convert.

Outer query is comparing the conveted date with getdate.

Thanks

quote:
Originally posted by tkizer

It means you've got "bad" data in the table. You've got data that can't be converted to a date/time data type. And the "bad" data isn't in the top 10000 rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-26 : 14:56:57

I do agree with Tara.
I believe that there are more than 10,000 records.
What gives a SELECT count(*) from record_dt?

No, you're never too old to Yak'n'Roll if you're too young to die.

sam4all
Starting Member

6 Posts

Posted - 2010-08-26 : 17:43:38

webfred:
Fred count of * gives 7885 records..
I am restating my previous reply

Step 1
Inner query (named inn) uses ISDATE to filters bad date records and converts the date in text to datetime format.. as column REL_DATE

If you run the inner(sub query) by itself it runs with out error..
(
select
convert(datetime,replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) REL_DATE
from record_dt
where len(reldate) > 4
and (isdate(replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) = 1)
)

Step 2
Now i compare the result set of Inner query with inn.REL_DATE with getdate. this is where i am getting the error.

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Use this sample data in a table you will know what i am talking about
2010 09 7
2010 0830
1999 01 26
1998 12 01
1999 03 09
2009 02 10
2008 10 28

Thanks

quote:
Originally posted by webfred

I do agree with Tara.
I believe that there are more than 10,000 records.
What gives a SELECT count(*) from record_dt?

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-27 : 14:21:48

the caveat of storing dates in improper formats.

you query is failing on this record

2010 0830

You need to do a thorough job of replacing the bad values if you expect to convert them to dates.

Short of that, you can use a temp table to hold intermediate data


Create Table #t (REL_DATE datetime);

insert #t
select
convert(datetime,replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) REL_DATE
from record_dt
where len(reldate) > 4 
and (isdate(replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) = 1);

select * from #t where REL_DATE < getdate()

drop table #t;

And Tara was right, the bad record(s) just aren't in the top 10,000. I don't believe there are less than 10,000 records in your record_dt table. In fact, using the sample data you posted, it still fails if i try to use top to filter out the bad one, or select more than exist.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2010-08-27 : 21:17:31

maybe you should try cleaning up the data by using update and change it to a proper date. That way the your tsql would be easier to write.

sam4all
Starting Member

6 Posts

Posted - 2010-08-31 : 14:24:59

Russel Ur right it is data issue. its failing because of 2010 0830 & 2010 09 7 records..
I agree I can fix it with temp tables or update ...there a lot of solution out there...
I am trying to understand how tsql is processing this query...

As said
select
convert(datetime,replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) REL_DATE
from record_dt
where len(reldate) > 4
and (isdate(replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) = 1);

Gives the records with right dates.(so it filters out records with bad dates)

My question all the members is why does the query below fail when the subquery (inn) eliminates invalid date records

select *
FROM (
(select
convert(datetime,replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) REL_DATE
from record_dt
where len(reldate) > 4
and (isdate(replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) = 1)) AS INN
WHERE INN.REL_DATE < GETDATE()

If any one can answer this i am sure its something to do with order of processing by the sql engine..
but can any one explain me the order....for this query...

Ideally what i would expect is, to process the inner query(fetches records with right dates)
and then compare the date with getdate()

Thanks

quote:
Originally posted by russell

the caveat of storing dates in improper formats.

you query is failing on this record

2010 0830

You need to do a thorough job of replacing the bad values if you expect to convert them to dates.

Short of that, you can use a temp table to hold intermediate data

Create Table #t (REL_DATE datetime);

insert #t
select
convert(datetime,replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) REL_DATE
from record_dt
where len(reldate) > 4
and (isdate(replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) = 1);

select * from #t where REL_DATE < getdate()

drop table #t;

And Tara was right, the bad record(s) just aren't in the top 10,000. I don't believe there are less than 10,000 records in your record_dt table. In fact, using the sample data you posted, it still fails if i try to use top to filter out the bad one, or select more than exist.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-31 : 14:42:36

The query engine doesn't operate in the order that you key in the code, nor in mathematical order -- meaning that parentheses don't force it to evaluate the query in the order you want it to.

solutions for this have already been posted.

sam4all
Starting Member

6 Posts

Posted - 2010-08-31 : 16:57:52

Russell

All I am trying to know now is, how the sql engine parses this query?...

In oracle the sql engine parses the inner query (called as inline view)and then uses the result to process the outer query.

When you use top in the inner query then tsql uses the inner query as inline view.

I am not sure why and how you got an error with the test data.

Anyway here is the script..

create table record_dt (reldate varchar(10));
insert into record_dt values ('2010 09 7 ');
insert into record_dt values ('2010 0830 ');
insert into record_dt values ('1999 01 26');
insert into record_dt values ('1998 12 01');
insert into record_dt values ('1999 03 09');
insert into record_dt values ('2009 02 10');
insert into record_dt values ('2008 10 28');

Query below does not DOES NOT WORK

select * from (
select
convert(datetime,replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) REL_DATE
from record_dt
where len(reldate) > 4
and (isdate(replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) = 1)
) as inn
where inn.rel_date<=getdate()

Query below WORKS

select * from (
select top 100
convert(datetime,replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) REL_DATE
from record_dt
where len(reldate) > 4
and (isdate(replace(replace(left(RelDate, 10), ' ', '-'), 'zz', '01')) = 1)
) as inn
where inn.rel_date<=getdate()

This solution works and is efficent then creating temp table
All i am tring to find is why how the engine works!!

drop table record_dt;

Let me know if u still beleive there more then 10000 records..

quote:
Originally posted by russell

The query engine doesn't operate in the order that you key in the code, nor in mathematical order -- meaning that parentheses don't force it to evaluate the query in the order you want it to.

solutions for this have already been posted.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-08-31 : 17:21:32

Somewhere, Itzik Ben-Gan has an article outlining exactly how this works, but I can't find it right now.

Check out these references though.

http://tsql.solidq.com/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf

http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources