MarTech

Using dates with SQL

Structured Query Language (SQL)

Working with dates in SQL is a pretty common task, and there are several date functions that we can use to query results and manipulate data.

NOW()

The NOW() function simply returns the current datetime, like this: 2023-01-18 23:06:03. You will probably use NOW() a lot. If you only need the current date or the current time, use CURDATE() or CURTIME(), respectively.

GETDATE()

The GETDATE() function works exactly like the NOW() function, except that GETDATE() returns milliseconds, too. The query result would look something like this: 2023-01-18 23:06:03.805.

DATE()

DATE(date)

Use the DATE() function to extract the date from a valid date expression. The return format is YYYY-MM-DD. This is a useful function when handling mismatched date formats, especially if you don’t need to work with time.

DATEADD()

DATEADD(interval, number, date)

The DATEADD() function is used to add (or subtract) two dates. The interval can be any of the following:

The number parameter should be the number of the interval parameter needed, and it can be either positive (future) or negative (past).

The following example returns a date value that’s eight days in the past, from the current moment in time.

DATEADD(day, -8, GETDATE())

DATEPART()

DATEPART(interval, date)

DATEPART() returns a specified part of a date. Use the interval parameter to specify the return value. The interval can be any of the following:

DATEDIFF()

DATEDIFF(interval, date1, date2)

Use the DATEDIFF function to return the difference between two dates. The return value can be any of the following specified intervals:

The function starts with the first date provided, and the result can be either positive or negative depending on which direction in time the calculation moved.

-- Find all people older than 40.
DATEDIFF(NOW(), dob)/365 > 40

An important thing to remember when working with dates in SQL is that date formats need to match between the database column and the intended result. For a primer on working with SQL, check out my introduction to SQL.