This chapter is from the book
This chapter is from the book
This chapter is from the book
Date/Time Functions
Date/Time functions allow for the manipulation of date and time values. The names of these functions differ, depending on the database used. In Microsoft SQL Server, the functions we’ll cover are called GETDATE, DATEPART, and DATEDIFF.
The simplest of the date/time functions is one that returns the current date and time. In Microsoft SQL Server, the function is named GETDATE. This function has no arguments. It merely returns the current date and time. For example:
SELECT GETDATE()
brings back an expression with the current date and time. Since the GETDATE function has no arguments, there is nothing specified between the parentheses. Remember that a date/time field is a special datatype that contains both a date and a time in a single field. An example of such a value is:
2017-05-15 08:48:30
This value refers to the 15th of May 2017, at 48 minutes and 30 seconds past 8 am.
The next date/time function enables us to analyze any specified date and return a value to represent such elements as the day or week of the date. Again, the name of this function differs, depending on the database. In Microsoft SQL Server, this function is called DATEPART. The general format is:
DATEPART(DatePart, DateValue)
The DateValue argument is any date. The DatePart argument can have many different values, including year, quarter, month, dayofyear, day, week, weekday, hour, minute, and second.
The following chart shows how the DATEPART function evaluates the date '5/6/2017', with different values for the DatePart argument:
DATEPART Function Expression |
Resulting Value |
DATEPART(month, '5/6/2017') |
5 |
DATEPART(day, '5/6/2017') |
6 |
DATEPART(week, '5/6/2017') |
18 |
DATEPART(weekday, '5/6/2017') |
7 |
Looking at the values in the previous chart, you can see that the month of 5/6/2017 is 5 (May). The day is 2 (Monday). The week is 18, because 5/6/2017 is in the 18th week of the year. The weekday is 7 because 5/6/2017 falls on a Saturday, which is the seventh day of the week.
The final date/time function we’ll cover, DATEDIFF, enables you to determine quantities such as the number of days between any two dates. The general format is:
DATEDIFF (DatePart, StartDate, EndDate)
Valid values for the DatePart argument for this function include year, quarter, month, dayofyear, day, month, hour, minute, and second. Here’s a chart that shows how the DATEDIFF function evaluates the difference between the dates 7/8/2017 and 8/14/2017, with different values for the DatePart argument:
DATEPART Function Expression |
Resulting Value |
DATEDIFF(day, '7/8/2017', '8/14/2017’) |
37 |
DATEDIFF(week, '7/8/2017', '8/14/2017’) |
6 |
DATEDIFF(month, '7/8/2017', '8/14/2017’) |
1 |
DATEDIFF(year, '7/8/2017', '8/14/2017’) |
0 |
The above chart indicates that there are 37 days, or 6 weeks, or 1 month, or 0 years between the two dates.