The type of result is a combination of true and false parts of IIF. According to documentation, the highest type is datetimeoffset, therefore it works like this: iif(@inputString like @txPattern, try_cast(@inputString as datetimeoffset), cast(try_cast(@inputString as datetime2) as datetimeoffset))
. The final conversion from datetime2 to datetimeoffset causes the seen form of result with “+00:00”. If you execute select cast(try_cast(@inputString as datetime2) as datetimeoffset)
, you will observe the same value.
Discrepancy in results when running TSQL query with iif
Hi,
SQL server version: running on Azure Sql Database
12.0.2000.8 RTM SQL Azure
DB compatibility level 160
I am getting discrepancy with the following results, the same thing with case statement, and with patindex.
declare @inputString nvarchar(max) = N'1970-01-01T00:00:00.987'
declare @txPattern nvarchar(max) = N'%[+-][0-9][0-9]:[0-9][0-9]'
select iif(@inputString like @txPattern, 'true', 'false') -- always returns false
select iif(@inputString like @txPattern, try_cast(@inputString as datetimeoffset), try_cast(@inputString as datetime2)) -- returns 1970-01-01 00:00:00.9870000 +00:00, which is the true part of the iif statement
-
Viorel 116.5K Reputation points
2024-07-20T17:53:53.8066667+00:00
2 additional answers
Sort by: Most helpful
-
Bruce (SqlWork.com) 63,501 Reputation points
2024-07-20T17:43:03.98+00:00 The pattern matches a string that ends with an offset (ex: “+12:34”) but the string literal does not. Try
declare @inputString nvarchar(max) = N'1970-01-01T00:00:00.987 +12:34'
-
LiHongMSFT-4306 26,621 Reputation points
2024-07-22T01:39:29.54+00:00 Hi @Mansa
See this doc:
Returns the data type with the highest precedence from the types in true_value and false_value.
Therefore, the return value will always be
datetimeoffset
datatype between datetimeoffset and datetime2, cause the datetimeoffset has higher precedence.For more information, see Data Type Precedence (Transact-SQL).
Best regards,
Cosmog
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".