Welcome back! This post is a followup to the post about string functions. The difference is this time, we’re discussing functions that manipulate dates.
GETDATE
The most often used function I can think of, with the possible exception of COUNT(*) is GETDATE(). When called this gives you the current datestamp from the server. This gives you the datestamp for right now. Store the result of this in a variable and you can pull any part of that datestamp or the whole thing. There are no parameters to pass, just call:
GETDATE()
DATEADD
After you have a way to find out what the current datestamp is, you’ll probably need to do things like tell what the date will be in 3 days, or 7 years ago. To do any addition (or subtraction) with a date and an amount of time, you’ll need DATEADD.
DATEADD(datePart, number, date)
The datePart can be any one of the following (passed in single quotes):
| Datepart | Abbreviations |
|---|---|
| Year | yy, yyyy |
| quarter | qq, q |
| Month | mm, m |
| dayofyear | dy, y |
| Day | dd, d |
| Week | wk, ww |
| Hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
The number can be any INTEGER, positive, negative, or zero.
The date is any valid DATE or DATETIME value. It can be a date column, variable, or literal DATETIME. A literal value would be something you pass as a hardcoded value. It is always wrapped in single quotes ‘5/25/1977’. By the way +5 points if you know the significance of that date.
DATEDIFF
Now, if you need to know how far apart two dates are, you have to use DATEDIFF instead of DATEADD.
DATEDIFF(datePart, startDate, endDate)
DatePart accepts the same values that I listed for DATEADD. StartDate and endDate are any two valid datetimes. They can be variables, columns, or literals.
DAY
If you have a DATESTAMP and you only want the day portion of it, use the DAY() function, and it will return an INTEGER form of the day.
DAY(date)
MONTH
If you have a DATESTAMP and you only want the month portion of it, use the MONTH() function, and it will return an INTEGER form of the month.
MONTH(date)
YEAR
If you have a DATESTAMP and you only want the year portion of it, use the YEAR() function, and it will return an INTEGER form of the YEAR.
YEAR(date)
DATEPART
Similar to the DAY, MONTH, and YEAR functions DATEPART will return an INTEGER version of the datePart you request.
| Datepart | Abbreviations |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| weekday | dw |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
Notice you can ask for dateParts other than MONTH, DAY, and YEAR. The dayofyear value comes into play when writing reports using “Julian” dates, except the definition of Julian date they use is the number of days since the start of the year. If you need actual Julian date, there are some custom functions I’d like to show you.
DATENAME
The DATENAME function is simlar to the DAY, MONTH, and YEAR functions, but it returns a character string rather than an INTEGER.
DATENAME(datePart, date)
This datePart can use any one of the following (slightly different than the previous list):
| Datepart | Abbreviations |
|---|---|
| year | yy, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| weekday | dw |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
The noticeable difference in this function is the weekday parameter, this function will actually return Sunday, Monday…. Also Month will return January, February, etc. This function can be useful when writing reports and you want to beautify the output!
Conclusion
The point of this post is to expose you to transformations you can do with DATETIME variables. You can combine these functions in very interesting ways! Feel free to experiment. And as always, if you have questions, please send them in!
| Previous: String Manipulation Functions | Next: System Functions |