-2

i have date column in table (documentyear). It has format yyyy-mm-dd . For example 2017-11-17. How can i get only year? i.e. convert from 2017-11-17 to 2017

psysky
  • 3,037
  • 5
  • 28
  • 64
  • Hint : `Year()` or `datepart(year, col)`. – Yogesh Sharma Aug 20 '18 at 12:14
  • Try - `LEFT(colname,4)` – Abhishek Aug 20 '18 at 12:15
  • https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017 – Y.S Aug 20 '18 at 12:16
  • 2
    Are you saying that you are storing the date as a string? Why? You should store it as a `DATE`. A `DATE` has no format. You can use date functions on a `DATE` column to format it or extract parts, like with `YEAR(mydate)`. – Thorsten Kettner Aug 20 '18 at 12:18
  • Nitpicking: A `date` data type column doesn't have a *format*. – Filburt Aug 20 '18 at 12:18
  • [Try this link. This should satisfy your current task.](https://stackoverflow.com/questions/12436743/how-to-extract-only-the-year-from-the-date-in-sql-server-2008) – EdwardKenway Aug 20 '18 at 12:20
  • if it is a string , then make it into an ISO date format by removing the hyphens, then cast to date, then apply year select year(CAST(REPLACE(YourField,'-','') as datetime2)) – Cato Aug 20 '18 at 12:51

3 Answers3

4

Try this: Year function will give you value of year

select year('2017-11-17')
Fahmi
  • 37,315
  • 5
  • 22
  • 31
4

Assuming it is a date, you can do:

select year(documentyear)             -- for an integer
select datename(year, documentyear)   -- for a string
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • how can i update table? i.e. that all table will be updated by this condition year(documentyear) – psysky Aug 20 '18 at 13:27
  • @G-spot . . . This question is about extracting the year. If you have another question about updating, that would be quite different. – Gordon Linoff Aug 20 '18 at 16:05
1

you need year function of mssql server

     year（'2018-05-12'）
    it will return 2018
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63