

Discover more from Data Analysis Journal
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: