'Strange behavior with TSQL year() and month() functions

I don't know what is going on here. The first expression in the select list produces 6 but yet the logical expression in the where clause is true.

Applies to SQL Server 2012, 2017 and 2019.

Simplified demo:

select
  'March 2022',
  202203-100*year(getdate())+month(getdate()),
  202203-cast(100*year(getdate())+month(getdate())as int)
where
  202203=100*year(getdate())+month(getdate())
┌──────────┬─┬─┐
│March 2022│6│0│
└──────────┴─┴─┘

Best Regards,

Esa

sql-server


Solution 1:[1]

We all like math, sure, but why not just:

DECLARE @i int = CONVERT(char(6), GETDATE(), 112);
PRINT @i;

Result:

202203

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Aaron Bertrand

Related Questions