April 8, 2008 at 1:52 pm
I have the following a view on a SQL2K box that uses the following SELECT statement:
SELECT SF.SKU,
SAT.PublicationDate AS SATPubDate,
SAM.PublicationDate AS SAMPubDat
FROM SkuFlags SF
LEFT OUTER JOIN SpringArbor_ttlsparb SAT ON SF.ISBN = SAT.ISBN
LEFT OUTER JOIN SpringArbor_music SAM ON SF.ISBN = SAM.PrimaryKey
WHERE (
(
( SAT.PublicationDate IS NOT NULL ) AND
( SAT.PublicationDate <> '010001' ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAT.PublicationDate, 2) + '/01/' + RIGHT(SAT.PublicationDate, 4) AS DATETIME) )))
)
OR (
( SAM.PublicationDate <> '010001' ) AND
( SAM.PublicationDate IS NOT NULL ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAM.PublicationDate, 2) + '/01/' + RIGHT(SAM.PublicationDate, 4) AS DATETIME)))
)
)
The view works in SQL2K. When I try to run it under SQL2K5, I get a "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." error. I know what the error is, the SAM.PublicationDate field has NULL values in it (and this is vendor supplied data that is updated frequently, so not dealing with NULL values isn't an option), so during the CAST function it's try to CAST NULL + /01/ + NULL into a DATETIME value and crashing.
My question is why this works in SQL2K and not SQL2K5?
Thanks,
Kevin
April 8, 2008 at 3:00 pm
that's probably due to SET CONCAT_NULL_YIELDS_NULL being OFF in your SS2K5 db/session. when set ON, null + '/01/'+ null will result in null (and cast/convert properly) rather than '/01/'.
April 8, 2008 at 3:15 pm
I tried toggling the SET CONCAT_NULL_YIELDS_NULL setting in a query window.
SET CONCAT_NULL_YIELDS_NULL ON I get:
Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
SET CONCAT_NULL_YIELDS_NULL OFF I get:
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting datetime from character string.
Again the same query works with the CONCAT_NULL_YIELDS_NULL ON or OFF under SS2K.
Thanks for the info though...
April 8, 2008 at 3:58 pm
kevin.bullen (4/8/2008)
I tried toggling the SET CONCAT_NULL_YIELDS_NULL setting in a query window.SET CONCAT_NULL_YIELDS_NULL ON I get:
Msg 242, Level 16, State 3, Line 4
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
SET CONCAT_NULL_YIELDS_NULL OFF I get:
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting datetime from character string.
Again the same query works with the CONCAT_NULL_YIELDS_NULL ON or OFF under SS2K.
Thanks for the info though...
OK, so nulls aren't the only issue.
select cast('/01/' as datetime)
go
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.
select cast('1/1/0001' as datetime)
go
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Msg 242 is being caused by a bad date value being encounted, most likely due to conversion of '010001' to '01/01/0001' which is outside of datetime's minimum value, 1/1/1753). Can you alter the view or is it part of the vendor's software?
If you can alter it, then you should convert GETDATE() to the date format used by the "PublicationDate"s via
[font="Courier New"]convert(char(2),getdate(),101) + convert(char(4),getdate(),112)[/font]
rather than try to convert their "PublicationDate"s to real dates.
-- replace the where below
WHERE (
(
( SAT.PublicationDate IS NOT NULL ) AND
( SAT.PublicationDate <> '010001' ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAT.PublicationDate, 2)
+ '/01/' + RIGHT(SAT.PublicationDate, 4) AS DATETIME) )))
)
OR (
( SAM.PublicationDate <> '010001' ) AND
( SAM.PublicationDate IS NOT NULL ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAM.PublicationDate, 2)
+ '/01/' + RIGHT(SAM.PublicationDate, 4) AS DATETIME)))
)
)
-- with this; the other comparisons are redundant since comparison to null is always false
-- and getdate() in mmyyyy format will never be less than '010001'
WHERE (
(convert(char(2),getdate()+1,101) + convert(char(4),getdate()+1,112))
<= SAT.PublicationDate
OR (convert(char(2),getdate()+1,101) + convert(char(4),getdate()+1,112))
<= SAM.PublicationDate )
As for why it 'works' in 2000 but not 2005, it could be the 2005 optimizer happens to evaluate the AND comparison that attempts to convert '010001' to a date before it evalutes the <> '010001' comparison. Remember that in SQL A and B and C can be evaluted in any order and could wind up being executed as B and A and C.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply