Part 3: Date Functions
After twenty years in IT, I can confirm that dates and times can be notoriously difficult to work with. Thankfully, modern relational databases like SQL Server provide a wealth of highly useful functions for this purpose. In today's blog, we'll explore some of the most popular ones.
Getting the Current Date and Time
Every programmatic language requires a way to get the current date and/or time. In SQL Server, there are a couple of ways to get the current date and time, via the CURRENT_TIMESTAMP and GETDATE() functions. Both return the current date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format:
So, why the two functions? As you can see in the above screenshot, GETDATE() requires parentheses, while CURRENT_TIMESTAMP does not. That makes it ideal for setting the default value of auditing fields such as create and last modified columns:
DATEPART
Being able to get the current date and time is one thing, but sometimes you need to parse out individual date parts. That's where the DATEPART() function comes in. It returns a specified part of a date as an integer value. Here's its syntax:
DATEPART(interval, date)
The interval parameter has to be a specific date part or abbreviation. For example, the year can be expressed as either year, yyyy, or yy. Here's the full list:
- year, yyyy, yy = Year
- quarter, qq, q = Quarter
- month, mm, m = month
- dayofyear, dy, y = Day of the year
- day, dd, d = Day of the month
- week, ww, wk = Week
- weekday, dw, w = Weekday
- hour, hh = hour
- minute, mi, n = Minute
- second, ss, s = Second
- millisecond, ms = Millisecond
The following query that breaks the current date into its day, month, and year constituents:
DATEFROMPARTS
Date/time functions can also help us construct a date from disparate pieces of data. It accepts a year, month, and day as input parameters and combines them to form a complete date:
DATEFROMPARTS(year, month, day)
Here's an example:
DATEADD
Adding and subtracting date/time intervals to and from a date is among the most common operations on dates. In SQL Server, the function to do that is DATEADD. It accepts three input parameters: the interval to add, how many, and the date to apply the intervals to:
DATEADD(interval, number, date)
The intervals accepted by DATEADD are identical to those of DATEPART, which we say earlier, so I won't repeat them here. Instead, let's take a look at a couple of examples of this important function.
Our first example adds three months to today's date:
To subtract an interval, just provide a negative number parameter:
DATEDIFF
Our last function return the difference between two date values, as expressed by the provided interval (see above for the full list of accepted values):
DATEDIFF(interval, date1, date2)
The following query returns the difference between two dates in months:
The first date would normally be considered to be the earlier one, so if the second date parameter precedes the first, then the DATEDIFF result is expressed as a negative value:
Conclusion
In today's blog, we covered some of the most important SQL Server date and time functions. In the next and final installment, we'll be looking at miscellaneous functions.
Interested in Navicat for SQL Server? You can try it for 14 days completely free of charge for evaluation purposes!
Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.
