Colleen Morrow is a SQL Server Consultant at UpSearch, a provider of DBA resources in northeastern Ohio. She has worked in the IT industry for 20 years, doing everything from technical support to development to database administration on Informix, Oracle, and SQL Server platforms.

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:

SELECT DATEPART(dw, GETDATE()) as GetWeekDay

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

SET DATEFIRST 4
SELECT DATEPART(dw, GETDATE()) as GetNewWeekDay

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.