Strange behavior with TSQL year() and month() functions-CodePudding

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

CodePudding user response：

Algebra basics:

x - (y * 100   z) <=> x - y * 100 - z

So it should be one of these:

select 202203 - (100 * year(getdate())   month(getdate())); -- 0
select 202203 -  100 * year(getdate()) - month(getdate());  -- 0

CodePudding user response：

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

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

Result:

Example db<>fiddle