SQL 101 – Date Manipulation Functions

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):

DatepartAbbreviations
Yearyy, yyyy
quarterqq, q
Monthmm, m
dayofyeardy, y
Daydd, d
Weekwk, ww
Hourhh
minutemi, n
secondss, s
millisecondms

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.

DatepartAbbreviations
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw
hourhh
minutemi, n
secondss, s
millisecondms

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):

DatepartAbbreviations
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw
hourhh
minutemi, n
secondss, s
millisecondms

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 FunctionsNext: System Functions

Tags: , ,

6 Responses to “SQL 101 – Date Manipulation Functions”

  1. Joris 20051124 at 13:27 #

    Yep this page goes right to the Favorites menu… Thanks!

  2. guptamurali 20051124 at 14:27 #

    its really good stuff. thanks

  3. Shishir Gupta 20051124 at 16:27 #

    The date and time format is an headache. It has become pain in my neck.

  4. Todd S 20130319 at 08:41 #

    5/25/77 was the opening day for Star Wars :-)

    • Shannon Lowder 20130627 at 09:38 #

      You rock! You’re the first to get it. Thanks for stopping by. I promise new content is coming soon!

Trackbacks/Pingbacks

  1. SQL 201 – String Manipulation Functions | Shannon Lowder - 20110209

    [...] Next: Date Manipulation Functions [...]

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.