88

In sql server 2008, how to extract only the year from the date. In DB I have a column for date, from that I need to extract the year. Is there any function for that?

Praveen
  • 55,303
  • 33
  • 133
  • 164
  • possible duplicate of [showing only the year portion of a date](http://stackoverflow.com/questions/6628942/showing-only-the-year-portion-of-a-date) – Ben Sep 15 '12 at 10:54

9 Answers9

128
year(@date)
year(getdate())
year('20120101')

update table
set column = year(date_column)
whre ....

or if you need it in another table

 update t
   set column = year(t1.date_column)
     from table_source t1
     join table_target t on (join condition)
    where ....
heretolearn
  • 6,387
  • 4
  • 30
  • 53
Dumitrescu Bogdan
  • 7,127
  • 2
  • 23
  • 31
  • Here I dont want for current datetime. I already have a column for datetime. How to have a query to extract that? – Praveen Sep 15 '12 at 10:53
  • This works; how can I store this year in another column in the table? Better to have a single line query. – Praveen Sep 15 '12 at 11:00
  • Can you please explain about the following error: Arithmetic overflow error converting expression to data type datetime. – Praveen Sep 15 '12 at 11:09
  • I guess you are trying to cast a string to a date. And the server cannot convert it to datetime type because it does not support the format. – Dumitrescu Bogdan Sep 15 '12 at 11:14
17
select year(current_timestamp)

SQLFiddle demo

juergen d
  • 201,996
  • 37
  • 293
  • 362
14

You can use year() function in sql to get the year from the specified date.

Syntax:

YEAR ( date )

For more information check here

heretolearn
  • 6,387
  • 4
  • 30
  • 53
5
year(table_column)

Example:

select * from mytable where year(transaction_day)='2013'
Emil Vikström
  • 90,431
  • 16
  • 141
  • 175
jkarim
  • 51
  • 1
  • 2
4

SQL Server Script

declare @iDate datetime
set @iDate=GETDATE()

print year(@iDate) -- for Year

print month(@iDate) -- for Month

print day(@iDate) -- for Day
UJS
  • 853
  • 1
  • 10
  • 16
1

Simply use

SELECT DATEPART(YEAR, SomeDateColumn)

It will return the portion of a DATETIME type that corresponds to the option you specify. SO DATEPART(YEAR, GETDATE()) would return the current year.

Can pass other time formatters instead of YEAR like

  • DAY
  • MONTH
  • SECOND
  • MILLISECOND
  • ...etc.
Aaron S.
  • 31
  • 2
1

the year function dose, like this:

select year(date_column) from table_name

Aa.mbi
  • 19
  • 4
  • 2
    There already 6+ year old answers that say this (with more details). When answering old questions, make sure your answer offers a substantially different or better quality answer than the existing answers. – Mark Rotteveel Jan 13 '19 at 12:49
0

DATEPART(yyyy, date_column) could be used to extract year. In general, DATEPART function is used to extract specific portions of a date value.

Vikdor
  • 23,934
  • 10
  • 61
  • 84
0
---Lalmuni Demos---
create table Users
(
userid int,date_of_birth date
)
insert into Users values(4,'9/10/1991')

select DATEDIFF(year,date_of_birth, getdate()) - (CASE WHEN (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()),date_of_birth)) > getdate() THEN 1 ELSE 0 END) as Years, 
MONTH(getdate() - (DATEADD(year, DATEDIFF(year, date_of_birth, getdate()), date_of_birth))) - 1 as Months, 
DAY(getdate() - (DATEADD(year, DATEDIFF(year,date_of_birth, getdate()), date_of_birth))) - 1 as Days,
from users
RAS
  • 8,100
  • 16
  • 64
  • 86
Lalmuni Singh
  • 9
  • 1