SELECT 'Happy '+ DATENAME(dw, GETDATE())

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.

SET LANGUAGE Español;
GO
SELECT DATENAME(mm, GETDATE()) 

In this case, the result is “Diciembre”.

SET LANGUAGE English;
GO
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.