If you had asked about the most important skill for a data scientist before I got my first job, my answer would certainly be Python. No doubt!
I work as a data scientist now and if you ask me the same question, my answer is still Python. However, I have second thoughts. What makes me think twice is SQL.
Sql is a must-have skill for data scientists. It is mainly used for querying relational databases but it is able to perform much more. SQL is equipped with so many functionalities that make it a highly efficient data analysis and manipulation tool.
In this article, we will go over 5 SQL functions that are used for manipulating dates. They come in handy when working with data that involves date or time based information.
Note: SQL is used by many relational database management systems such as MySQL, SQL Server, PostgreSQL, and so on. Although they mostly adopt the same SQL syntax, there might be small differences. In this article, we will be using SQL Server.
1. Getdate
As its name suggests, the getdate function gives us today’s date. Let’s do an example.
DECLARE @mydate Date
SET @mydate = GETDATE()
print @mydate
'2021-08-22'
We create a variable called "mydate" and assign its value as today’s date.
2. Dateadd
The name of this function is even more self-explanatory than the previous one. The dateadd function is used for adding a time or date interval to a date.
As always, the syntax is easier to understand with examples.
DECLARE @mydate Date
SET @mydate = GETDATE()
SELECT DATEADD(MONTH, 1, @mydate) AS NextMonth
NextMonth
2021-09-22
The first parameter indicates the interval and second one is the number of intervals. The third parameter is the base value.
DATEADD(interval, number of intervals, date)
We can use other intervals as well.
DECLARE @mydate Date
SET @mydate = GETDATE()
SELECT DATEADD(WEEK, -2, @mydate) AS TwoWeeksBefore
TwoWeeksBefore
2021-08-08
If you put a minus sign before the number of intervals, it subtracts the specified interval from the given date.
It is possible to add time-based intervals but we need to use a datetime variable.
DECLARE @mydate DateTime
SET @mydate = GETDATE()
SELECT DATEADD(HOUR, 10, @mydate)
'2021-08-23 00:10:17.287'
3. Datediff
The datediff function is used for calculating the difference between two dates based on a given interval.
DECLARE @mydate Date
SET @mydate = '2018-08-08'
SELECT DATEDIFF(MONTH, @mydate, GETDATE())
36
The mydate variable holds the value "2018–08–08". In the select statement, we calculate the difference between this variable and the current date.
Just like the dateadd function, the datediff function accepts other intervals as well.
DECLARE @mydate Date
SET @mydate = '2021-10-08'
SELECT DATEDIFF(DAY, @mydate, GETDATE()) AS DayDifference
DayDifference
47
4. Datename
The datename function can be used for extracting the parts of a date. For instance, we can get the name of month and day from a date as follows:
DECLARE @mydate Date
SET @mydate = '2021-10-08'
SELECT DATENAME(MONTH, @mydate)
October
SELECT DATENAME(WEEKDAY, @mydate)
Friday
5. Year, Month, and Day
The year, month, and day are separate functions but I think it is better to cover them together.
We have covered the datename function which gives us month and day names. In some cases, we need this information as numbers. The year, month, and day functions allow for decomposing a date.
Let’s do an example that demonstrates how they can be used.
DECLARE @mydate Date
SET @mydate = '2021-10-08'
SELECT
Date = @mydate,
Year = YEAR(@mydate),
Month = MONTH(@mydate),
Day = DAY(@mydate)
Date Year Month Day
2021-10-08 2021 10 8
We now have access to each part of the given date.
Conclusion
Manipulating dates is important for data analysis especially when working with time series data. SQL provides several functions to make this process simple and efficient.
The functions in this article cover a substantial amount of operation that you will need to do with dates.
Thank you for reading. Please let me know if you have any feedback.