Discrepancy in results when running TSQL query with iif

Mansa 20 Reputation points
2024-07-20T16:53:49.1566667+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,617 questions
0 comments
Accepted answer
  1. Viorel 116.5K Reputation points
    2024-07-20T17:53:53.8066667+00:00

    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.

    2 people found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. 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'

    0 comments
  2. 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".

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.