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: 2024-12-15 00:06:35. 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: 2024-12-15 00:06:35.081.
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:
- year (also yyyy or yy)
- quarter (also qq or q)
- month (also mm or m)
- dayofyear (also dy or y)
- day (also dd or d)
- week (also ww or wk)
- weekday (also dw or w)
- hour (also hh)
- minute (also mi or n)
- second (also ss or s)
- millisecond (also ms)
- microsecond (also mcs)
- nanosecond (also ns)
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:
- year (also yyyy or yy)
- quarter (also qq or q)
- month (also mm or m)
- dayofyear (also dy or y)
- day (also dd or d)
- week (also ww or wk)
- weekday (also dw or w)
- hour (also hh)
- minute (also mi or n)
- second (also ss or s)
- millisecond (also ms)
- microsecond (also mcs)
- nanosecond (also ns)
- tzoffset (timezone offset, also tz)
- iso_week (ISO week, also isowk or isoww)
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:
- year (also yyyy or yy)
- quarter (also qq or q)
- month (also mm or m)
- dayofyear
- day (also dy or y)
- week (also ww or wk)
- weekday (also dw or w)
- hour (also hh)
- minute (also mi or n)
- second (also ss or s)
- millisecond (also ms)
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 > 40An 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.