如何只从 sql 服务器2008中的日期提取年份？

在 sqlserver2008中，如何只从日期中提取年份。 在 DB 中我有一个日期列，从中我需要提取年份。 有什么作用吗？

517806 次浏览
小开 
select year(current_timestamp)

SQLFiddle demo

小开
最佳答案 
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 ....
小开

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

小开

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

Syntax:

YEAR ( date )

For more information check here

小开 
---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
小开 
year(table_column)

Example:

select * from mytable where year(transaction_day)='2013'
小开

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.
小开

SQL Server Script

declare @iDate datetime
set @iDate=GETDATE()


print year(@iDate) -- for Year


print month(@iDate) -- for Month


print day(@iDate) -- for Day
小开

the year function dose, like this:

select year(date_column) from table_name


提交答案