SQL Date-Time Cheatsheet
A short guide to steps, solution, and must-know terminology
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)
SELECT DATEPART(MONTH, created_at) AS MonthCreated;
SELECT DATEPART(WEEKDAY, created_at) as WeekDayCreated
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)
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:
LEFT OUTER JOIN order
ON order.item_id = item.id
AND DATEADD(day,7,order.created_at::DATE) = item.created_at::DATE
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 )
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):
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))
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: