How to only get Year as part of date / datetime in SQL Server

Question

When I create a date type in SQL, the result will come out like this 2012-2-1 Monday 00:00:00

However, I only want the year.

How can I get this?

Solution 1

Make an numeric field with just the year, or use the YEAR() function.

Solution 2

You can use this query to get current year.

select year(now());

Solution 3

Could you use the following?:

SELECT * 
FROM
table_name
WHERE CONVERT(DATE, column_name) >= '2017-01-01'
AND
CONVERT (DATE, column_name) <= '2017-03-31';

But, you will probably only have to use the CONVERT part, as the above query gets you data between 2 specific dates.

It should just be the 'DATE' part that is causing the problems, along with how you inserted the data. If you inserted the dates in the format of 'YYYY-MM-DD', it should work properly.

Solution 4

datetime, includes date and time, "date" just includes date formats.

select datepart(year,getdate())

View more solutions