-1

Assume OrderDate is a date. What are the differences between YEAR(OrderDate) and DATEPART(year,OrderDate) (or DATEPART(yyyy,OrderDate) or DATEPART(yy,OrderDate) )? Thanks.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Tim
  • 1
  • 141
  • 372
  • 590
  • You should start with the documentation. DATEPART - https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql and Year - https://learn.microsoft.com/en-us/sql/t-sql/functions/year-transact-sql – Sean Lange Nov 08 '17 at 17:39
  • Have you read the documentation??? Before SQL Server 2008, you have to use `DATEPART` since there's no `YEAR()`. – Eric Nov 08 '17 at 17:43
  • 1
    @Eric - are you sure? I'm sure the functions such as `YEAR` existed at least as far back as 2000. Don't get confused by MS documentation that revises history and tells you the earliest *supported* version of the product (currently) – Damien_The_Unbeliever Nov 08 '17 at 17:50
  • 1
    @Eric Don't believe the documentation when it says `SQL Server (starting with 2008)`, that is because the authors of the documentation don't usually bother to verify if any version prior to that supported the function. `year()` has been around for a long time, probably before it was called SQL Server. – SqlZim Nov 08 '17 at 17:50

3 Answers3

4

They are all the same:

SELECT DATEPART(yy,GETDATE()), DATEPART(yyyy,GETDATE())
     , DATEPART(year, GETDATE()), YEAR(GETDATE());

As from doc

year -> yy, yyyy (abbreviations)

I would use DATEPART with year following EIBTI principle (Explicit Is Better than Implicit)

There is one more possibility ODBC function:

SELECT  {fn YEAR(GETDATE())};

For example to get HOUR you cannot use built-in function:

SELECT HOUR(GETDATE())
-- error

SELECT DATEPART(hour, GETDATE())
-- datepart function

SELECT {fn HOUR(GETDATE())}
-- will work

Rextester Demo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
1

I try to consider compatibility with other language, including SQL flavors. DATEPART is supported in other Microsoft products like C and VB. But Year() is supported in MySQL. So whcih to choose depends on your ecosystem and what you may end up migrating your code to.

n8.
  • 1,732
  • 3
  • 16
  • 38
  • 1
    As work continues on any project, and especially when dealing with datetime handling in SQL products, the chances of it being interoperable approaches zero. Fairly rapidly. – Damien_The_Unbeliever Nov 08 '17 at 18:17
1

Just for fun, DateName() returns the string value.

Example

Select 'Year '+datename(year,GetDate())

Returns

Year 2017
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66