Working with timestamps and data formats in SQL can be tricky. Often dates are loaded and stored as strings, var, or else in databases. And yet, date-time values are the most common data manipulations in SQL. Below I’ll cover 3 important must-know date functions like DATEPART(), DATEADD(), DATEDIFF(), which often are asked during SQL coding interviews, and will share some handy date extraction solutions or formattings I applied in my sql for data analysis over last month.
DATEPART() or DATE_PART()
DATEPART() - retrieves day, month, or year from the dataset:
DATEPART (datepart , date)
Example:
SELECT DATEPART(MONTH, created_at) AS MonthCreated;
SELECT DATEPART(WEEKDAY, created_at) as WeekDayCreated
DATEADD()
DATEADD() - adds or subtracts a specific period from a given date. You should use it when you want to add or subtract, for example, 10 days/months from the current date.
DATEADD (datepart, number, date)
Example:
SELECT DATEADD(day, 28, '2020/01/25') AS DateAdd;
SELECT DATEADD(month, -2, '2020/09/25') AS DateAdd;
You can also apply it as a condition for a JOIN:
FROM item
LEFT OUTER JOIN order
ON order.item_id = item.id
AND DATEADD(day,7,order.created_at::DATE) = item.created_at::DATE
DATEDIFF()
DATEDIFF() - returns the difference in days, months, years, or hours between two dates.
You should use it to find out how many days have passed between two dates.
DATEDIFF ( datepart , startdate , enddate )
Example:
SELECT DATEDIFF(MONTH,'September','November') AS MonthsDiff
SELECT DATEDIFF(WEEK, '2020-05-01','2020-09-31') AS WeeksDiff
🔍 A roundup of other common date functions and formatting (for RedShift and PostgreSQL):
DATE(created_at)
created_at >= CURRENT_DATE - INTERVAL '15 days'
created_at > GETDATE() - 60 (or created_at::DATE if date is not formatted)
DATE_TRUNC('month', created_at) (or DATE_TRUNC('month', created_at::DATE if date is not formatted)
MIN(created_at) / MAX(created_at)
EXTRACT(MINUTE FROM (item.created_at::TIMESTAMP - order.created_at::TIMESTAMP))
select LAST_DAY(created_at)
NEXT_DAY('2020-01-01', 'Friday')
DATE_PART_YEAR(created_at)
Let me know if you stuck with SQL or need help with formatting or special data extraction - I have a big love for SQL 😍
If you haven’t yet, make sure to subscribe to learn more about sql functions: