|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL server 2005 date conversion bug??I have a very strange problem converting a string to a date... See the following query select [RTG MDY ISSUER RATING], mdy_rating_date, convert(datetime,mdy_rating_date,103) rate_date from issuer_industry_ratings_tmp I join issuer_alias BLOOM on I.bb_ticker = BLOOM.alias and BLOOM.alias_type = 'BBEQTK' and mdy_rating_date is not null ----- RTG MDY ISSUER RATING mdy_rating_date rate_date WR 26/10/2004 2004-10-26 00:00:00.000 WR 07/07/2005 2005-07-07 00:00:00.000 WR 07/07/2005 2005-07-07 00:00:00.000 WR 11/12/2003 2003-12-11 00:00:00.000 WR 07/07/2005 2005-07-07 00:00:00.000 That works ok and converts my date mdy_rating_date from string to datetime Now if I remove the completely unrelated column [RTG MDY ISSUER RATING] from the query. select --[RTG MDY ISSUER RATING], mdy_rating_date, convert(datetime,mdy_rating_date,103) rate_date from issuer_industry_ratings_tmp I join issuer_alias BLOOM on I.bb_ticker = BLOOM.alias and BLOOM.alias_type = 'BBEQTK' and mdy_rating_date is not null Then I get a conversion error with my date field!!! Msg 241, Level 16, State 1, Line 1 Conversion failed when converting datetime from character string. What's going on here? This cannot be explained with normal logic. Thanks, Panos. Can you provide DDL and sample data?
Show quote "Panos Stavroulis." <PanosStavrou***@discussions.microsoft.com> wrote in message news:1659E209-CC0B-45AD-8B61-E0745F2396CD@microsoft.com... > Hi, > > I have a very strange problem converting a string to a date... > > See the following query > > select > [RTG MDY ISSUER RATING], > mdy_rating_date, > convert(datetime,mdy_rating_date,103) rate_date > from issuer_industry_ratings_tmp I > join issuer_alias BLOOM > on I.bb_ticker = BLOOM.alias > and BLOOM.alias_type = 'BBEQTK' > and mdy_rating_date is not null > > ----- > RTG MDY ISSUER RATING mdy_rating_date rate_date > WR 26/10/2004 2004-10-26 00:00:00.000 > WR 07/07/2005 2005-07-07 00:00:00.000 > WR 07/07/2005 2005-07-07 00:00:00.000 > WR 11/12/2003 2003-12-11 00:00:00.000 > WR 07/07/2005 2005-07-07 00:00:00.000 > > > That works ok and converts my date mdy_rating_date from string to datetime > > Now if I remove the completely unrelated column [RTG MDY ISSUER RATING] > from > the query. > > select > --[RTG MDY ISSUER RATING], > mdy_rating_date, > convert(datetime,mdy_rating_date,103) rate_date > from issuer_industry_ratings_tmp I > join issuer_alias BLOOM > on I.bb_ticker = BLOOM.alias > and BLOOM.alias_type = 'BBEQTK' > and mdy_rating_date is not null > > Then I get a conversion error with my date field!!! > Msg 241, Level 16, State 1, Line 1 > Conversion failed when converting datetime from character string. > > What's going on here? This cannot be explained with normal logic. > > Thanks, > > Panos. See if you get any rows for this?
select mdy_rating_date from issuer_industry_ratings_tmp I join issuer_alias BLOOM on I.bb_ticker = BLOOM.alias and BLOOM.alias_type = 'BBEQTK' and mdy_rating_date is not null where isdate(mdy_rating_date) = 0 Without seeing the table DDL and sample data, it's only a guess.
My WAG is that you have a data interpretation problem. There may be one or more rows where the string value cannot be converted to a valid datetime. This could be bad data OR incorrect DateFormat. One of the issues with conveting string data data to datetime data is "How will the conversion process know that '11/12/2003' is to be Nov OR Dec? -this is especially important for 26/10/2004." What is the DateFormat for the application running the query? You may find this helpful: http://www.karaszi.com/SQLServer/info_datetime.asp -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Panos Stavroulis." <PanosStavrou***@discussions.microsoft.com> wrote in message news:1659E209-CC0B-45AD-8B61-E0745F2396CD@microsoft.com... > Hi, > > I have a very strange problem converting a string to a date... > > See the following query > > select > [RTG MDY ISSUER RATING], > mdy_rating_date, > convert(datetime,mdy_rating_date,103) rate_date > from issuer_industry_ratings_tmp I > join issuer_alias BLOOM > on I.bb_ticker = BLOOM.alias > and BLOOM.alias_type = 'BBEQTK' > and mdy_rating_date is not null > > ----- > RTG MDY ISSUER RATING mdy_rating_date rate_date > WR 26/10/2004 2004-10-26 00:00:00.000 > WR 07/07/2005 2005-07-07 00:00:00.000 > WR 07/07/2005 2005-07-07 00:00:00.000 > WR 11/12/2003 2003-12-11 00:00:00.000 > WR 07/07/2005 2005-07-07 00:00:00.000 > > > That works ok and converts my date mdy_rating_date from string to datetime > > Now if I remove the completely unrelated column [RTG MDY ISSUER RATING] > from > the query. > > select > --[RTG MDY ISSUER RATING], > mdy_rating_date, > convert(datetime,mdy_rating_date,103) rate_date > from issuer_industry_ratings_tmp I > join issuer_alias BLOOM > on I.bb_ticker = BLOOM.alias > and BLOOM.alias_type = 'BBEQTK' > and mdy_rating_date is not null > > Then I get a conversion error with my date field!!! > Msg 241, Level 16, State 1, Line 1 > Conversion failed when converting datetime from character string. > > What's going on here? This cannot be explained with normal logic. > > Thanks, > > Panos. Yes there was an offending row which wouldn't convert to a date it was
something like 'NA#'. However why adding an unrelated column in the select list would make a difference and it would return results; it should have failed in both situations don't you think? All the columns are nvarchar. I've removed the dodgy row and now works ok but doesn't make sense why the 2 queries produced different results. Thanks. CREATE TABLE [dbo].[issuer_industry_ratings_tmp]( [bb_ticker] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Industry Sector] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Industry Group] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RTG MDY ISSUER RATING] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RTG SP LT FC ISSUER CREDIT] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [mdy_rating_date] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RTG SP OUTLOOK] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RTG MDY OUTLOOK] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RTG SP OUTLOOK DT] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RTG MDY OUTLOOK DT] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) Show quote "Arnie Rowland" wrote: > Without seeing the table DDL and sample data, it's only a guess. > > My WAG is that you have a data interpretation problem. There may be one or > more rows where the string value cannot be converted to a valid datetime. > This could be bad data OR incorrect DateFormat. > > One of the issues with conveting string data data to datetime data is "How > will the conversion process know that '11/12/2003' is to be Nov OR > Dec? -this is especially important for 26/10/2004." > > What is the DateFormat for the application running the query? > > You may find this helpful: > > http://www.karaszi.com/SQLServer/info_datetime.asp > > -- > Arnie Rowland, YACE* > "To be successful, your heart must accompany your knowledge." > > *Yet Another Certification Exam > > > "Panos Stavroulis." <PanosStavrou***@discussions.microsoft.com> wrote in > message news:1659E209-CC0B-45AD-8B61-E0745F2396CD@microsoft.com... > > Hi, > > > > I have a very strange problem converting a string to a date... > > > > See the following query > > > > select > > [RTG MDY ISSUER RATING], > > mdy_rating_date, > > convert(datetime,mdy_rating_date,103) rate_date > > from issuer_industry_ratings_tmp I > > join issuer_alias BLOOM > > on I.bb_ticker = BLOOM.alias > > and BLOOM.alias_type = 'BBEQTK' > > and mdy_rating_date is not null > > > > ----- > > RTG MDY ISSUER RATING mdy_rating_date rate_date > > WR 26/10/2004 2004-10-26 00:00:00.000 > > WR 07/07/2005 2005-07-07 00:00:00.000 > > WR 07/07/2005 2005-07-07 00:00:00.000 > > WR 11/12/2003 2003-12-11 00:00:00.000 > > WR 07/07/2005 2005-07-07 00:00:00.000 > > > > > > That works ok and converts my date mdy_rating_date from string to datetime > > > > Now if I remove the completely unrelated column [RTG MDY ISSUER RATING] > > from > > the query. > > > > select > > --[RTG MDY ISSUER RATING], > > mdy_rating_date, > > convert(datetime,mdy_rating_date,103) rate_date > > from issuer_industry_ratings_tmp I > > join issuer_alias BLOOM > > on I.bb_ticker = BLOOM.alias > > and BLOOM.alias_type = 'BBEQTK' > > and mdy_rating_date is not null > > > > Then I get a conversion error with my date field!!! > > Msg 241, Level 16, State 1, Line 1 > > Conversion failed when converting datetime from character string. > > > > What's going on here? This cannot be explained with normal logic. > > > > Thanks, > > > > Panos. > > > I cannot reproduce it on my server, yet I have some ideas:
1. Removing a column from a select list might have resulted in a different execution plan. For instance, the original query could scan the whole table, while the second one might use index covering or scan the table via an index. 2. Suppose the original query was scanning the whole table, and suppose it was converting before applying the filter. Then the incorrect value would blow up the first query, but not the second, because the second one would not touch the offending row at all. Greetings Alexander. I am hoping you know Alexey Ostrovsky. He was
working on the Favorites Assistant for me and all of sudden disappeared from email contact about a month ago. I have not heard from him since. If you do know Alexey, please contact me at bobemail1s-gro***@yahoo.com Thanks, Bob Flanagan Macro Systems 144 Dewberry Drive Hockessin, Delaware, U.S. 19707 Phone: 302-234-9857, cell 302-584-1771 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel Alexander Kuznetsov wrote: Show quote > I cannot reproduce it on my server, yet I have some ideas: > 1. Removing a column from a select list might have resulted in a > different execution plan. For instance, the original query could scan > the whole table, while the second one might use index covering or scan > the table via an index. > 2. Suppose the original query was scanning the whole table, and suppose > it was converting before applying the filter. Then the incorrect value > would blow up the first query, but not the second, because the second > one would not touch the offending row at all. Glad you were able to find the issue. And yes, it 'should' have failed in
both queries. It would probably take more time that worth the effort to determine why. Good luck. -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Panos Stavroulis." <PanosStavrou***@discussions.microsoft.com> wrote in message news:42BFAF53-5251-4E44-8C74-468CC7B556A0@microsoft.com... > Yes there was an offending row which wouldn't convert to a date it was > something like 'NA#'. > > However why adding an unrelated column in the select list would make a > difference and it would return results; it should have failed in both > situations don't you think? All the columns are nvarchar. I've removed the > dodgy row and now works ok but doesn't make sense why the 2 queries > produced > different results. Thanks. > > CREATE TABLE [dbo].[issuer_industry_ratings_tmp]( > [bb_ticker] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, > [Industry Sector] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL, > [Industry Group] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL, > [RTG MDY ISSUER RATING] [nvarchar](255) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL, > [RTG SP LT FC ISSUER CREDIT] [nvarchar](255) COLLATE > SQL_Latin1_General_CP1_CI_AS NULL, > [mdy_rating_date] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL, > [RTG SP OUTLOOK] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL, > [RTG MDY OUTLOOK] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL, > [RTG SP OUTLOOK DT] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL, > [RTG MDY OUTLOOK DT] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS > NULL > ) > > "Arnie Rowland" wrote: > >> Without seeing the table DDL and sample data, it's only a guess. >> >> My WAG is that you have a data interpretation problem. There may be one >> or >> more rows where the string value cannot be converted to a valid datetime. >> This could be bad data OR incorrect DateFormat. >> >> One of the issues with conveting string data data to datetime data is >> "How >> will the conversion process know that '11/12/2003' is to be Nov OR >> Dec? -this is especially important for 26/10/2004." >> >> What is the DateFormat for the application running the query? >> >> You may find this helpful: >> >> http://www.karaszi.com/SQLServer/info_datetime.asp >> >> -- >> Arnie Rowland, YACE* >> "To be successful, your heart must accompany your knowledge." >> >> *Yet Another Certification Exam >> >> >> "Panos Stavroulis." <PanosStavrou***@discussions.microsoft.com> wrote in >> message news:1659E209-CC0B-45AD-8B61-E0745F2396CD@microsoft.com... >> > Hi, >> > >> > I have a very strange problem converting a string to a date... >> > >> > See the following query >> > >> > select >> > [RTG MDY ISSUER RATING], >> > mdy_rating_date, >> > convert(datetime,mdy_rating_date,103) rate_date >> > from issuer_industry_ratings_tmp I >> > join issuer_alias BLOOM >> > on I.bb_ticker = BLOOM.alias >> > and BLOOM.alias_type = 'BBEQTK' >> > and mdy_rating_date is not null >> > >> > ----- >> > RTG MDY ISSUER RATING mdy_rating_date rate_date >> > WR 26/10/2004 2004-10-26 00:00:00.000 >> > WR 07/07/2005 2005-07-07 00:00:00.000 >> > WR 07/07/2005 2005-07-07 00:00:00.000 >> > WR 11/12/2003 2003-12-11 00:00:00.000 >> > WR 07/07/2005 2005-07-07 00:00:00.000 >> > >> > >> > That works ok and converts my date mdy_rating_date from string to >> > datetime >> > >> > Now if I remove the completely unrelated column [RTG MDY ISSUER RATING] >> > from >> > the query. >> > >> > select >> > --[RTG MDY ISSUER RATING], >> > mdy_rating_date, >> > convert(datetime,mdy_rating_date,103) rate_date >> > from issuer_industry_ratings_tmp I >> > join issuer_alias BLOOM >> > on I.bb_ticker = BLOOM.alias >> > and BLOOM.alias_type = 'BBEQTK' >> > and mdy_rating_date is not null >> > >> > Then I get a conversion error with my date field!!! >> > Msg 241, Level 16, State 1, Line 1 >> > Conversion failed when converting datetime from character string. >> > >> > What's going on here? This cannot be explained with normal logic. >> > >> > Thanks, >> > >> > Panos. >> >> >> I could not reproduce this issue using the following simplified repro:
CREATE TABLE dbo.ab_issuer_industry_ratings_tmp ( [RTG MDY ISSUER RATING] CHAR(2), mdy_rating_date CHAR(10) ) GO SET NOCOUNT ON; INSERT dbo.ab_issuer_industry_ratings_tmp SELECT 'WR', '26/10/2004' INSERT dbo.ab_issuer_industry_ratings_tmp SELECT 'WR', '07/07/2005' INSERT dbo.ab_issuer_industry_ratings_tmp SELECT 'WR', '07/07/2005' INSERT dbo.ab_issuer_industry_ratings_tmp SELECT 'WR', '11/12/2003' INSERT dbo.ab_issuer_industry_ratings_tmp SELECT 'WR', '07/07/2005' GO select [RTG MDY ISSUER RATING], mdy_rating_date, convert(datetime,mdy_rating_date,103) rate_date from dbo.ab_issuer_industry_ratings_tmp; GO select --[RTG MDY ISSUER RATING], mdy_rating_date, convert(datetime,mdy_rating_date,103) rate_date from dbo.ab_issuer_industry_ratings_tmp; GO DROP TABLE dbo.ab_issuer_industry_ratings_tmp; So, I am not sure how the join against issuer_alias may be affecting things here, but my guess is thatyour two queries are more different than just removing one of the columns (one of the queries is likely bringing back data that is not a date, or one of the queries is running from dateformat mdy and the other is running from dmy). Tell me what this yields: set dateformat mdy select count(*) from issuer_industry_ratings_tmp WHERE isdate(mdy_rating_date) = 0; set dateformat dmy select count(*) from issuer_industry_ratings_tmp WHERE isdate(mdy_rating_date) = 0; As a suggestion, be sure to use the table aliases as prefixes for all columns in the query. And store datetime data in a datetime column! I don't understand why so many people want to store dates in varchar columns. Show quote "Panos Stavroulis." <PanosStavrou***@discussions.microsoft.com> wrote in message news:1659E209-CC0B-45AD-8B61-E0745F2396CD@microsoft.com... > Hi, > > I have a very strange problem converting a string to a date... > > See the following query > > select > [RTG MDY ISSUER RATING], > mdy_rating_date, > convert(datetime,mdy_rating_date,103) rate_date > from issuer_industry_ratings_tmp I > join issuer_alias BLOOM > on I.bb_ticker = BLOOM.alias > and BLOOM.alias_type = 'BBEQTK' > and mdy_rating_date is not null > > ----- > RTG MDY ISSUER RATING mdy_rating_date rate_date > WR 26/10/2004 2004-10-26 00:00:00.000 > WR 07/07/2005 2005-07-07 00:00:00.000 > WR 07/07/2005 2005-07-07 00:00:00.000 > WR 11/12/2003 2003-12-11 00:00:00.000 > WR 07/07/2005 2005-07-07 00:00:00.000 > > > That works ok and converts my date mdy_rating_date from string to datetime > > Now if I remove the completely unrelated column [RTG MDY ISSUER RATING] > from > the query. > > select > --[RTG MDY ISSUER RATING], > mdy_rating_date, > convert(datetime,mdy_rating_date,103) rate_date > from issuer_industry_ratings_tmp I > join issuer_alias BLOOM > on I.bb_ticker = BLOOM.alias > and BLOOM.alias_type = 'BBEQTK' > and mdy_rating_date is not null > > Then I get a conversion error with my date field!!! > Msg 241, Level 16, State 1, Line 1 > Conversion failed when converting datetime from character string. > > What's going on here? This cannot be explained with normal logic. > > Thanks, > > Panos. Thanks for this! I actually had to move on and fix it but will try to
re-insert the data from the excel file and see if it happens again in another table. That answers your question about the varchar! it's only a tmp table to insert my data from excel, I would never store a date in a non date field!! Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > I could not reproduce this issue using the following simplified repro: > > > > > CREATE TABLE dbo.ab_issuer_industry_ratings_tmp > ( > [RTG MDY ISSUER RATING] CHAR(2), > mdy_rating_date CHAR(10) > ) > GO > > SET NOCOUNT ON; > INSERT dbo.ab_issuer_industry_ratings_tmp SELECT 'WR', '26/10/2004' > INSERT dbo.ab_issuer_industry_ratings_tmp SELECT 'WR', '07/07/2005' > INSERT dbo.ab_issuer_industry_ratings_tmp SELECT 'WR', '07/07/2005' > INSERT dbo.ab_issuer_industry_ratings_tmp SELECT 'WR', '11/12/2003' > INSERT dbo.ab_issuer_industry_ratings_tmp SELECT 'WR', '07/07/2005' > GO > > select > [RTG MDY ISSUER RATING], > mdy_rating_date, > convert(datetime,mdy_rating_date,103) rate_date > from dbo.ab_issuer_industry_ratings_tmp; > GO > > select > --[RTG MDY ISSUER RATING], > mdy_rating_date, > convert(datetime,mdy_rating_date,103) rate_date > from dbo.ab_issuer_industry_ratings_tmp; > GO > > DROP TABLE dbo.ab_issuer_industry_ratings_tmp; > > > So, I am not sure how the join against issuer_alias may be affecting things > here, but my guess is thatyour two queries are more different than just > removing one of the columns (one of the queries is likely bringing back data > that is not a date, or one of the queries is running from dateformat mdy and > the other is running from dmy). Tell me what this yields: > > > set dateformat mdy > select count(*) from issuer_industry_ratings_tmp WHERE > isdate(mdy_rating_date) = 0; > set dateformat dmy > select count(*) from issuer_industry_ratings_tmp WHERE > isdate(mdy_rating_date) = 0; > > > As a suggestion, be sure to use the table aliases as prefixes for all > columns in the query. And store datetime data in a datetime column! I > don't understand why so many people want to store dates in varchar columns. > > > > > > > > > > "Panos Stavroulis." <PanosStavrou***@discussions.microsoft.com> wrote in > message news:1659E209-CC0B-45AD-8B61-E0745F2396CD@microsoft.com... > > Hi, > > > > I have a very strange problem converting a string to a date... > > > > See the following query > > > > select > > [RTG MDY ISSUER RATING], > > mdy_rating_date, > > convert(datetime,mdy_rating_date,103) rate_date > > from issuer_industry_ratings_tmp I > > join issuer_alias BLOOM > > on I.bb_ticker = BLOOM.alias > > and BLOOM.alias_type = 'BBEQTK' > > and mdy_rating_date is not null > > > > ----- > > RTG MDY ISSUER RATING mdy_rating_date rate_date > > WR 26/10/2004 2004-10-26 00:00:00.000 > > WR 07/07/2005 2005-07-07 00:00:00.000 > > WR 07/07/2005 2005-07-07 00:00:00.000 > > WR 11/12/2003 2003-12-11 00:00:00.000 > > WR 07/07/2005 2005-07-07 00:00:00.000 > > > > > > That works ok and converts my date mdy_rating_date from string to datetime > > > > Now if I remove the completely unrelated column [RTG MDY ISSUER RATING] > > from > > the query. > > > > select > > --[RTG MDY ISSUER RATING], > > mdy_rating_date, > > convert(datetime,mdy_rating_date,103) rate_date > > from issuer_industry_ratings_tmp I > > join issuer_alias BLOOM > > on I.bb_ticker = BLOOM.alias > > and BLOOM.alias_type = 'BBEQTK' > > and mdy_rating_date is not null > > > > Then I get a conversion error with my date field!!! > > Msg 241, Level 16, State 1, Line 1 > > Conversion failed when converting datetime from character string. > > > > What's going on here? This cannot be explained with normal logic. > > > > Thanks, > > > > Panos. > > >  |
|||||||||||||||||||||||
Other interesting topics