April 26, 2018 at 11:13 pm
Comments posted to this topic are about the item Filtering a CAST
April 27, 2018 at 8:12 am
I knew that only two rows met the criteria. But the case statement was so convoluted an answer of returning an error seemed possible. Oh well.
April 27, 2018 at 8:54 am
The answers 2 and 3 had me second guessing myself. I knew that only 3 rows would be converted. Took me a second to realize the > condition.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 30, 2018 at 6:09 am
Good head-scratcher, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
April 30, 2018 at 12:36 pm
First off, there is no need for the convoluted case statement. TRY_CAST returns the succesful CAST when successful and returns NULL when unsuccessful, so the following would have been simpler and just as effective:SELECT CAST(Myval AS datetime)
FROM CastTest AS ct
WHERE TRY_CAST(ct.Myval AS datetime) > '2000-01-01';
Second, using a CASE to guard a CAST is an effective strategy (since SQL will not promote the evaluation of the THEN clauses of a CASE above the WHEN clause valiation), but it needs to be extended to the SELECT clause as well. While the logical query evaluation order indicates that the WHERE clause should be evaluated and records filtered before the SELECT clause is evaluated, the physical query evaluation order is free to reorder as it pleases. As a result, the code you used and the code I have above could result in a conversion error that fails the whole query if the optimizer decides to evaluate the CAST(Myval AS datetime) in the SELECT clause prior to filtering the rows. This is unlikely in the supplied query, but I have seen production apps that ran for years without a problem suddenly get bit by this issue. Any time there is any chance that even a single value could fail a CAST, whether or not that value would be returned in the results, use TRY_CAST! The following code should be safe:SELECT TRY_CAST(Myval AS datetime)
FROM CastTest AS ct
WHERE TRY_CAST(ct.Myval AS datetime) > '2000-01-01';
Third, I got the question right, but for the wrong reasons. I assumed that the '123' and '123.5' values would be interpreted as being in early 1900 (as 123 and 123.5 would be) and then be excluded by the WHERE clause, but I was wrong. To see the behavior I assumed, one would need to use something like this:SELECT COALESCE(TRY_CAST(Myval AS datetime), TRY_CAST(TRY_CAST(Myval AS float) AS datetime))
FROM CastTest AS ct;
Finally, if one wants to use the above code in the WHERE clause, I would typically write that like so, using the CROSS APPLY to avoid repeating the conversion code:SELECT C1.MyvalDatetime
FROM dbo.CastTest AS ct
CROSS APPLY ( SELECT
COALESCE(TRY_CAST(ct.Myval AS datetime),
TRY_CAST(TRY_CAST(ct.Myval AS float) AS datetime)) AS MyvalDatetime
) AS C1
WHERE C1.MyvalDatetime > '2000-01-01';
May 4, 2018 at 4:21 am
Nice question, but when you use country specific date / time conversion, you should always add a SET language = 'englisch' to the query.
On my German SQL Server the query would return only one row, since 'Mar 23, 2000' is no valid German datetime format.
God is real, unless declared integer.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply