CODEWITHSUNDEEP
sql
Reynan
Reynan

Reputation: 261

how to compare year from date value type in database to today's year

How to compare the year today from database year(date value type)

Here is the data in database > 2012-11-13 00:00:00.000

SELECT COUNT(DISTINCT applicationId)
FROM custleaseapplication
WHERE @dateOfApplication(year) == year today

Upvotes: 1

Views: 3845

Answers (3)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131512

While 

WHERE YEAR(DateColumn) = YEAR(GetDate())

will get the data you want, it will also force the database to do a full table scan instead of using any indexes on the column.

If year is the actual 4-digit year, you can simply write

WHERE Year >= YEAR(GetDate())  AND Year < YEAR(GetDate()) +1

This will work if Year is a numeric type.

If the column is a text type, it will probably work as SQL Server will convert the result of YEAR() to text before comparing. Differences in collation could prevent the server from using any underlying indexes though, forcing a table scan.

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460168

You can use GetDate to get the current DateTime in sql-server and you can use YEAR to get the year of a datetime as int.

SELECT COUNT(DISTINCT applicationId) 
FROM custleaseapplication 
WHERE YEAR(DateColumn) = YEAR(GetDate())

Upvotes: 3

ttaaoossuu
ttaaoossuu

Reputation: 7894

WHERE YEAR(@dateOfApplication) = YEAR(GETDATE())

Upvotes: 1

Related Questions