Skip to content

SQL 101 – Date Manipulation Functions

2005 September 25
tags: , ,
by Shannon Lowder

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
6 Responses leave one →
  1. Joris permalink
    November 24, 2005

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

  2. guptamurali permalink
    November 24, 2005

    its really good stuff. thanks

  3. Shishir Gupta permalink
    November 24, 2005

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

  4. Todd S permalink
    March 19, 2013

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

    • June 27, 2013

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

Trackbacks and Pingbacks

  1. SQL 201 – String Manipulation Functions | Shannon Lowder

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS