SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Strange error with date/time conversion in subquer
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hoffman
Starting Member

9 Posts

Posted - 11/19/2012 :  10:49:10  Show Profile  Reply with Quote
Hello everyone,

I'm developing a somewhat complex query but I have hit a very strange error with date/time conversion...

This was the error message I got (SQL Server 2012 RTM):
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.


And this is my current query:
SELECT pdiaria.*,
	(SELECT 
		MAX(CAST((SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) + '.000Z') AS datetime)) 
		FROM PRD 
		WHERE Maquina=pdiaria.Maquina 
			AND ((Data_I=pdiaria.Data_I AND SUBSTRING(Hora_I, 1, 2)>='05' AND ISDATE(Data_F)=1) OR (Data_F=CONVERT(varchar, DATEADD(day, 1, DTI), 112) AND SUBSTRING(Hora_F, 1, 2)<'05'))
			AND ISDATE(SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) + '.000Z')=1
			
	) AS uOF
FROM
	(SELECT Maquina, Data_I, CAST(Data_I as datetime) AS DTI FROM PRD WHERE ISDATE(Data_I)=1 AND SUBSTRING(Hora_I, 1, 2)>='05' AND ISDATE(Data_F)=1 GROUP BY Data_I, Maquina) pdiaria


In the PRD table, Data_I and Data_F are nvarchar dates in the ISO (yyyymmdd) format and Hora_I/Hora_F are nvarchar time values in hhmmss format. Sometimes both date and time values are zero.

I've been doing some testing and I'm pretty sure that the issue happens with the conversion inside the MAX aggregate in the inner subquery... However, as you can see, I tried adding a ISDATE check to make sure it is valid before trying to convert it but this did not seem to help.

Does anyone know why this may be happening? I'm quite clueless...

Thanks in advance!

Edited by - hoffman on 11/21/2012 08:14:30

jackv
Flowing Fount of Yak Knowledge

United Kingdom
1908 Posts

Posted - 11/19/2012 :  11:08:15  Show Profile  Visit jackv's Homepage  Reply with Quote
You could use ISDATE to filter out invalid data

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

hoffman
Starting Member

9 Posts

Posted - 11/19/2012 :  11:13:07  Show Profile  Reply with Quote
Hello Jack,

I'm using ISDATE to filter the whole datetime string in the inner subquery but it doesn't seem to help...
Would you do it differently?

Thanks
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5924 Posts

Posted - 11/19/2012 :  11:16:44  Show Profile  Reply with Quote
What I suggest you do first is select out the concatenated "date" results without the CAST and without the MAX just to see that the values are what you expect.
Can you convert one of these resulting strings to datetime? I suspect you should replace the "T" with a <space> and exclude the ".000Z"

Be One with the Optimizer
TG

Edited by - TG on 11/19/2012 11:20:38
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/19/2012 :  11:25:48  Show Profile  Reply with Quote
Some more thoughts to what TG already suggested:

Just by redaing through the query that you posted, I don't see anything obviously wrong - but the query is too complex for my mind to hold and process without some sample data to go with it. In general, my experience has been that without seeing the data, it is hard to guess what might be causing the error in such cases.

The easiest way I have found to see the data that causes the problem is to run the query, let it fail, and look at output in the results pane (if any). Examine the data in the source tables that correspond to the row that SHOULD HAVE BEEN in the output if there was no error. See what is special about that row that is causing the problem. Try to convert the Data_I column in that row to datetime using the code fragment you are using.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4351 Posts

Posted - 11/19/2012 :  16:56:36  Show Profile  Reply with Quote
quote:
Originally posted by hoffman

Sometimes both date and time values are zero.

How do you want to handle it when the Date is zero and/or the Time is zero?
Go to Top of Page

hoffman
Starting Member

9 Posts

Posted - 11/19/2012 :  17:03:22  Show Profile  Reply with Quote
Thank you for your replies.

Replacing the T with a space and removing the Z, as suggested by TG, did not solve this error. There are no results at all in the result pane.
Also, the final date strings seem to have a proper format and this query seems to be able to convert them to datetime in some cases (i.e. if I do a TOP 10) and display the expected results...

However, converting Data_F and Hora_F for all rows using the following query works perfectly:
SELECT CAST(SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) + '.000Z' as datetime) as Data_F FROM PRD WHERE ISDATE(Data_F)=1


Same goes for Data_I/Hora_I...

Any clues as to why this may be happening since, apparently, I can convert all of these date/time values as long as I check if the date is valid?
Although I do know a decent amount of SQL, I'm still a novice (especially at debugging, it seems) so any tips are welcome :)

Thanks
Go to Top of Page

hoffman
Starting Member

9 Posts

Posted - 11/19/2012 :  17:07:07  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

quote:
Originally posted by hoffman

Sometimes both date and time values are zero.

How do you want to handle it when the Date is zero and/or the Time is zero?



I'd like those rows to be excluded from the query. ISDATE() seems to do a good job at that (except, maybe, for the inner subquery...)
Go to Top of Page

hoffman
Starting Member

9 Posts

Posted - 11/21/2012 :  06:39:44  Show Profile  Reply with Quote
Does anyone have any other ideas or suggestions on how to further debug this?

I apologize for the bump, but I have to meet a deadline and I'm running out of time...
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/21/2012 :  06:42:18  Show Profile  Reply with Quote
At the risk of repeating myself, do what I suggested in my reply on 11/19/2012 : 11:25:48 to narrow it down to one of the rows that is causing the problem. Once you find the row, post the data.
Go to Top of Page

hoffman
Starting Member

9 Posts

Posted - 11/21/2012 :  08:37:26  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck
At the risk of repeating myself, do what I suggested in my reply on 11/19/2012 : 11:25:48 to narrow it down to one of the rows that is causing the problem. Once you find the row, post the data.


Pardon my inexperience with SQL debugging, but how would I do that when there is no output at all in the results pane?

I've managed to get this query working by changing the inner subquery to convert the maximum value to datetime, instead of converting the values first and only then selecting the max, i.e.:
CAST(MAX((SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) + '.000Z')) AS datetime)

This worked fine for all the rows in the table. Can you foresee any problems with using this approach?

It's very strange that it doesn't work the way around because I tried converting all valid (checked w/ ISDATE) Data_F and Hora_F values to datetime, as mentioned in my post at 11/19/2012 : 17:03:22, and this worked flawlessly...

Thanks!
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/21/2012 :  08:44:58  Show Profile  Reply with Quote
Based on the date format that you are using in the query you just posted, it should work correctly and give you the results you want. BUT, any time when something is not clear and we work around it, it is bound to cause problems later. So I would be very uncomfortable until the root cause can be figured out.

Given that you have one case that is working, can you try this approach?

1. Run the following query - it is logically identical to the query that you got working, except instead of MAX, I am taking the top 1.
SELECT TOP (1)
	CAST(
   (
       SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' 
       + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + 
       ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) 
       + '.000Z'
   )
            AS DATETIME)
FROM
	tbl
ORDER BY
       (
           SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' 
           + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + 
           ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) 
           + '.000Z'
       )
DESC


2. If that works, change top 1 to top 2 and then top 10 and then top 20 and so on until the code breaks. At that point, when it breaks, identify the row that caused it to break, and look at the values of Data_F and Hora_F to see what is special about those.

Edited by - sunitabeck on 11/21/2012 08:45:34
Go to Top of Page

hoffman
Starting Member

9 Posts

Posted - 11/21/2012 :  09:37:07  Show Profile  Reply with Quote
Thank you for your reply.

Running that query for the TOP 1 resulted in the same error. Oddly, the Data_F and Hora_F values for this row are '20121028' and '093432', respectively, which seem to be valid date and time values.

Adding a where clause to your query to check whether the date is valid (WHERE ISDATE(Data_F)=1), however, enables the query to convert the exact same row I mentioned above to datetime without any issues... Odd huh?

Any ideas on what to do from here?

Edited by - hoffman on 11/21/2012 09:39:41
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4351 Posts

Posted - 11/21/2012 :  10:28:21  Show Profile  Reply with Quote
There are lots of options, but since you haven't provided any sample data or expected output it makes it very hard for us to help. I'll offer up another suggestion that you might be able to use a CASE expression to help narrow things down. For example:
CASE
    WHEN Data_F = '0' OR Hora_F = '0' 
        THEN NULL
    WHEN NOT ISDATE
        (
           SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' 
           + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + 
           ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) 
           + '.000Z'
        )
        THEN NULL
    ELSE
        CAST
        (
           SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' 
           + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + 
           ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) 
           + '.000Z' AS DATETIME
        )        
END


If you want more help, here are some links that can help you provide us more information, like DDL, DML and expected output:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

hoffman
Starting Member

9 Posts

Posted - 11/21/2012 :  10:48:51  Show Profile  Reply with Quote
Lamprey: That actually did the trick, thanks!! I could have thought about doing something like that earlier...

I don't have much time right now but I'll have a look and post more info later as I'm intrigued by this issue.

Thanks again.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4351 Posts

Posted - 11/21/2012 :  10:49:46  Show Profile  Reply with Quote
quote:
Originally posted by sunitabeck

Based on the date format that you are using in the query you just posted, it should work correctly and give you the results you want. BUT, any time when something is not clear and we work around it, it is bound to cause problems later. So I would be very uncomfortable until the root cause can be figured out.

Given that you have one case that is working, can you try this approach?

1. Run the following query - it is logically identical to the query that you got working, except instead of MAX, I am taking the top 1.
SELECT TOP (1)
	CAST(
   (
       SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' 
       + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + 
       ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) 
       + '.000Z'
   )
            AS DATETIME)
FROM
	tbl
ORDER BY
       (
           SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' 
           + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + 
           ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) 
           + '.000Z'
       )
DESC


2. If that works, change top 1 to top 2 and then top 10 and then top 20 and so on until the code breaks. At that point, when it breaks, identify the row that caused it to break, and look at the values of Data_F and Hora_F to see what is special about those.

FYI, I don't think that will work as SQL will convert the value to a DATETIME before the sort.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/21/2012 :  11:28:16  Show Profile  Reply with Quote
quote:
FYI, I don't think that will work as SQL will convert the value to a DATETIME before the sort.
You are right, logically sort is the last operation. What I really meant is to do something like this, and if that works change the TOP (1) to TOP (10), or TOP (20) or whatever until it breaks:
SELECT CAST(col1 AS DATETIME) FROM 
(           
	SELECT TOP (1)
		   SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' 
		   + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + 
		   ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) 
		   + '.000Z' AS col1
	FROM
		tbl
	ORDER BY
		   (
			   SUBSTRING(Data_F, 1, 4) + '-' + SUBSTRING(Data_F, 5, 2) + '-' 
			   + SUBSTRING(Data_F, 7, 2) + 'T' + SUBSTRING(Hora_F, 1, 2) + 
			   ':' + SUBSTRING(Hora_F, 3, 2) + ':' + SUBSTRING(Hora_F, 5, 2) 
			   + '.000Z'
		   )
	DESC
) s
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000