Strange error with date/time conversion in subquer

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

General SQL Server Forums

New to SQL Server Programming

Strange error with date/time conversion in subquer

Author

Topic

hoffman
Starting Member

9 Posts

Posted - 2012-11-19 : 10:49:10

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!

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-11-19 : 11:08:15

You could use ISDATE to filter out invalid data

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

hoffman
Starting Member

9 Posts

Posted - 2012-11-19 : 11:13:07

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-19 : 11:16:44

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-19 : 11:25:48

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-19 : 16:56:36

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?

hoffman
Starting Member

9 Posts

Posted - 2012-11-19 : 17:03:22

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

hoffman
Starting Member

9 Posts

Posted - 2012-11-19 : 17:07:07

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...)

hoffman
Starting Member

9 Posts

Posted - 2012-11-21 : 06:39:44

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...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 06:42:18

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.

hoffman
Starting Member

9 Posts

Posted - 2012-11-21 : 08:37:26

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!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 08:44:58

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.

hoffman
Starting Member

9 Posts

Posted - 2012-11-21 : 09:37:07

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?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-21 : 10:28:21

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

hoffman
Starting Member

9 Posts

Posted - 2012-11-21 : 10:48:51

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-21 : 10:49:46

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-21 : 11:28:16

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources