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)

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: