Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Cleveland DBA

Colleen Morrow is a database professional living in Cleveland, OH who has been working with database systems since 1996. For more than 12 years, she was a Database Administrator at a large law firm where she developed an appreciation for auditing, automation, and performance tuning. Since that time she has worked with clients in the healthcare, manufacturing, software, and distribution/freight delivery industries. Colleen is currently a Senior Consultant at Fortified Data.

SQL Server A to Z – YEAR


Welcome to the penultimate installment of SQL Server A to Z (it’s not often I get to use “penultimate”). Officially, Y stands for Year, but that would be kinda boring, and quite short, so let’s run with it and talk about all sorts of date-parts-related stuff.

Functions that get parts of dates

Since this is, technically, a blog about the YEAR function, let’s begin with some functions that return parts of dates. The YEAR, MONTH, and DAY functions are pretty self-explanatory, returning the year, month, and day of the input date, respectively. An alternate method of retrieving this data would be to use the DATEPART function. Take a gander.

-- get the current year

SELECT YEAR(GETDATE()) as YearFunction, DATEPART(yy, GETDATE()) as DatepartFunction

-- get the current month

SELECT MONTH(GETDATE()) as MonthFunction, DATEPART(mm, GETDATE()) as DatepartFunction

-- and the day

SELECT DAY(GETDATE()) as DayFunction, DATEPART(dd, GETDATE()) as DatepartFunction

And you aren’t limited to your basic year/month/day components for DATEPART, either. You can pull out time components, like hour/minute/second, all the way down to nanoseconds. There’s also functionality for getting the weekday (dw), the day of the year (dy), the week of the year (wk), even the quarter (qq).

It should be noted that parts like weekday are impacted by whatever value you’re using for SET DATEFIRST. This setting specifies what weekday is considered the first day of the week. Accepted values are 1 (Monday) through 7 (Sunday). The default value for this setting in U.S. English is 7, indicating a Sunday start. So assuming a Sunday start, we see that Monday is day 2 of this week:


If I set DATEFIRST to 4, indicating a Thursday start, Monday is now the fifth day of the week.



Not sure what your current DATEFIRST setting is? @@DATEFIRST will tell you.

SELECT @@DATEFIRST as DateFirstSetting

Comparing and modifying parts of dates

To find the difference between 2 dates, such as the number of days, weeks, months, etc, we use the DATEDIFF function. Almost all of the same dateparts apply to this function, as well. For example, if we want to know the number of days until my birthday:

SELECT DATEDIFF(dd, GETDATE(), '2012-04-08') as ShoppingDaysLeft  

Please refer to my Amazon Wish List for ideas. When in doubt, bourbon is always the right size. Thanks.

We can also add or subtract dateparts to date values using the DATEADD function.

SELECT DATEADD(ww, -8, GETDATE()) as [8WeeksAgo] 

Playing with strings

The last function we’re going to cover is DATENAME. We can use DATENAME to get the character string for the datepart of a particular date. This value is dependent on the value you’re using for SET LANGUAGE.




In this case, the result is “Diciembre”.



SELECT DATENAME(dw, GETDATE()) as DayOfWeek, DATENAME(mm, GETDATE()) as ThisMonth 

And now we see “Monday” and “December” for our result. For a full list of supported languages, look at sys.syslanguages.

I hope you enjoyed this penultimate (had to get it in one more time) installment of SQL Server A to Z.  Stop back later in the week to close out the year and the series.


Leave a comment on the original post [, opens in a new window]

Loading comments...