There are so many different date functions within SQL, it can be hard to keep track of them all! However, they are some of the handiest functions, especially when dealing with time-series data.
While rebuilding some core data models, I came across a date function that I never heard of before. I figured there are probably others who have not heard of this function or even some of the others I’m going to mention.
Whether a Sql ninja or a beginner, this article will be a great refresher or introduction to using dates within your SQL code.
Timestampdiff()
Timestamp does exactly what it sounds like. It finds the difference between two timestamps.
This requires 3 different arguments- an interval and two date values.
timestampdiff(MONTH, '2021-12-01', '2021-12-31 06:03:15')
An interval can be Month
, Year
, Minute
, and Second
.
Input: 2 **** date or datetime value (dates are automatically read as having a time of 00:00:00) and an interval
Output: The time between the two dates in whatever interval you specified
This function is great for finding how much time has passed between two datetime columns.
Note that the first date value is subtracted from the second date value, making it positive. If we were to switch the two dates, we would get a negative value.
Datediff()
This is the same as the function before except now we are finding the difference between two dates rather than two date timestamps.
This still requires 3 different arguments- an interval and two date values.
datediff(MONTH, '2021-12-01', '2021-12-31 06:03:15')
An interval can be Month
, Year
, Minute
, and Second
.
Input: 2 **** date or datetime value (datetimes are automatically read as having a time of 00:00:00) and an interval
Output: The time between the two dates in whatever interval you specified
This function works almost the same way as the other, however, the timestamp in this example would be read as 2021-12-31 00:00:00
instead of having a time of 06:03:15
.
Month()
This function simply extracts the month from the date value that you feed it.
month('2021-12-01')
returns a value of 12
for the month of December.
Input: 1 date or datetime value
Output: number month from 1–12
I find myself using this one often when validating data in my data models. I use it to compare data from the same month, to ensure the row counts or aggregations match.
Year()
This function extracts the year from the date value that you feed it.
year('2021-12-01 01:12:45')
returns a value of 2021
for the year.
Input: 1 date or datetime value
Output: year
Again, I often use this in the data validation stage of my models. I will use it to compare larger amounts of data rather than for just one month.
Current_date()
This will give you the current date on the date that your SQL code is ran. So, for example, I am writing this blog post on December 4, 2021. This function would return 2021-12-04
.
current_date()
Input: none
Output: a date value
This function is helpful for calculating the time elapsed since a certain date column value. I often find myself using it in conjunction with datediff()
.
Date_add()
This function allows you to add values to a date column. You can add almost any unit of time to a date column. Hour
, Day
, Week
, Month
, and Year
just happen to be a few of the more popular units.
date_add('2021-12-01', INTERVAL 1 WEEK)
This code will add one week to the date to become 2021-12-08
.
Input: a date or datetime value, numeric value, unit of time
Output: a date or datetime value
Date_add() is helpful for creating date ranges or looking for a future value in time. It is one that I use often in my web sessions models.
Cast()
This is a classic function that I use nearly anytime I am dealing with dates, datetimes, or timestamps. It is helpful to do on the base columns being used in your SQL code, before using any of the functions mentioned above.
Cast('2021-12-01' AS timestampntz)
Input: column name in any date type
Output: column name in the specified date type
The CAST() function helps to create consistency across all time value columns. I had an issue in one of my models where I was using timstampnt values and timestamp values, not realizing this was throwing off my output. This function saved the day in casting to the same time value and giving me the correct output!
Datepart()
And, last but not least, the latest SQL date function that I just learned myself! I spotted this function in some code that I was rewriting and had no idea what it did. After some research, this is what I learned.
The datepart() function returns the specified portion of the time value column. It is similar to the month()
and year()
functions but allows you to choose what part of the date you are extracting.
datepart(year, '2021-12-01')
This piece of code will return 2021
as the output.
Input: unit of time, date or datetime value
Output: numeric value representing the specified unit of time
Datepart()
does seem to run a bit faster than month()
or year()
, so when running queries on a large amount of data, this function would be preferred.
Conclusion
Whether you’re a beginner or a pro with SQL, you now how a toolkit full of useful data functions. Keep them in your back pocket and you’ll be ready to tackle any tricky date problems that come your way.
It is the attention to detail that makes one a true SQL ninja. When you know multiple different functions, you can choose which is the best for your use case. This gives you flexibility in how you write your code and the speed at which it runs.
Check out How to Use SQL Cross Joins, How to Use SQL Window Functions, and Top Skills to Ace Every SQL Interview Question if you’re looking to uplevel your SQL coding game.