Skip to content
My Docs

Date and Time Functions

Difficulty: Intermediate
Generated on: 2025-07-10 02:25:40
Category: SQL Cheatsheet for Database Development

SQL Date and Time Functions - Intermediate Cheatsheet

Section titled “SQL Date and Time Functions - Intermediate Cheatsheet”

1. Quick Overview

Section titled “1. Quick Overview”

Date and time functions are essential for manipulating and working with date and time data in SQL databases. They allow you to extract parts of dates, perform calculations, format dates, and convert between different date and time formats. They are crucial for reporting, data analysis, scheduling, and many other database-driven applications. This cheat sheet covers intermediate-level date and time functions, focusing on practical applications and database-specific nuances.

2. Syntax

Section titled “2. Syntax”

The specific syntax varies slightly between database systems, but the core concepts are generally consistent.

General Functions:

  • CURRENT_DATE: Returns the current date.
  • CURRENT_TIME: Returns the current time.
  • CURRENT_TIMESTAMP: Returns the current date and time (timestamp).
  • NOW(): Returns the current date and time (often interchangeable with CURRENT_TIMESTAMP).
  • DATE(expression): Extracts the date part from a datetime expression.
  • TIME(expression): Extracts the time part from a datetime expression.

Extraction Functions:

  • EXTRACT(part FROM date): Extracts a specific part (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND) from a date or timestamp.
  • YEAR(date): Returns the year.
  • MONTH(date): Returns the month.
  • DAY(date) / DAYOFMONTH(date): Returns the day of the month.
  • HOUR(time): Returns the hour.
  • MINUTE(time): Returns the minute.
  • SECOND(time): Returns the second.
  • DAYOFWEEK(date): Returns the day of the week (Sunday=1, Saturday=7 in some systems, varies).
  • DAYOFYEAR(date): Returns the day of the year.
  • WEEK(date): Returns the week number.

Calculation Functions:

  • DATE_ADD(date, INTERVAL value unit)/ DATE_SUB(date, INTERVAL value unit): Adds/Subtracts a time interval to/from a date.
  • DATEDIFF(unit, date1, date2): Returns the difference between two dates in the specified unit.

Formatting Functions:

  • DATE_FORMAT(date, format): Formats a date according to the specified format string.

Conversion Functions:

  • STR_TO_DATE(string, format): Converts a string to a date according to the specified format string.
  • CAST(expression AS data_type): Converts an expression to a specified data type (including date and time).

3. Common Use Cases

Section titled “3. Common Use Cases”
  • Reporting: Generating reports based on date ranges (e.g., sales per month, orders placed last week).
  • Data Analysis: Analyzing trends over time (e.g., website traffic patterns, customer behavior).
  • Scheduling: Scheduling tasks based on specific dates and times (e.g., sending reminders, running batch jobs).
  • Data Cleaning: Standardizing date formats and correcting inconsistencies.
  • Age Calculation: Determining the age of a person based on their birthdate.
  • Order Fulfilment: Calculating expected delivery dates based on shipping times.
  • Logging: Recording timestamps for events and actions.
  • Expiration Dates: Tracking expiration dates for products, subscriptions, or licenses.
  • Calculating Time Between Events: Determining the duration between two timestamps, for example, time to resolution for a support ticket.

4. Best Practices

Section titled “4. Best Practices”
  • Use appropriate data types: Store date and time data in dedicated date, time, or timestamp columns (e.g., DATE, TIME, DATETIME, TIMESTAMP). Avoid storing dates as strings.
  • Index date columns: Indexing date columns improves query performance, especially for range queries (e.g., WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31').
  • Use parameterized queries: Avoid SQL injection vulnerabilities by using parameterized queries or prepared statements when dealing with user-provided date values.
  • Consider time zones: Be aware of time zones and handle them appropriately, especially in applications that serve users in different geographical locations. Use CONVERT_TZ (MySQL) or equivalent functions in other databases if necessary.
  • Optimize calculations: Avoid complex calculations within the WHERE clause when possible. Consider pre-calculating and storing derived date values in separate columns if performance is critical.
  • Use consistent formatting: Ensure that date formats are consistent throughout your application to avoid parsing errors. Use DATE_FORMAT and STR_TO_DATE judiciously.
  • Test thoroughly: Test your date and time queries with a variety of input values, including edge cases (e.g., leap years, end-of-month dates).
  • Be mindful of database-specific functions: While many functions are similar across databases, be aware of syntax differences and available functions in your specific database system.
  • Use UTC for storage: Store all dates and times in UTC (Coordinated Universal Time) in the database. This simplifies handling time zones in your application logic. Convert to local time zones only for display.

5. Examples

Section titled “5. Examples”

Sample Data:

Let’s assume we have a table called orders with the following structure:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATETIME,
    ship_date DATETIME
);


INSERT INTO orders (order_id, customer_id, order_date, ship_date) VALUES
(1, 101, '2023-10-26 10:00:00', '2023-10-28 14:30:00'),
(2, 102, '2023-10-27 12:30:00', '2023-10-29 16:00:00'),
(3, 101, '2023-10-28 15:00:00', '2023-10-30 18:00:00'),
(4, 103, '2023-10-29 09:00:00', '2023-10-31 11:00:00'),
(5, 102, '2023-10-30 11:00:00', '2023-11-01 13:00:00');

Examples:

-- Get the current date and time
SELECT CURRENT_TIMESTAMP;


-- Extract the year from the order_date
SELECT order_id, YEAR(order_date) AS order_year FROM orders;


-- Extract the month from the order_date
SELECT order_id, MONTH(order_date) AS order_month FROM orders;


-- Extract the day of the month
SELECT order_id, DAY(order_date) AS order_day FROM orders;


-- Calculate the difference in days between order_date and ship_date
SELECT order_id, DATEDIFF(DAY, order_date, ship_date) AS shipping_time_days FROM orders;


-- Calculate the difference in hours between order_date and ship_date (SQL Server)
SELECT order_id, DATEDIFF(HOUR, order_date, ship_date) AS shipping_time_hours FROM orders;


-- Calculate the difference in hours between order_date and ship_date (MySQL)
SELECT order_id, TIMESTAMPDIFF(HOUR, order_date, ship_date) AS shipping_time_hours FROM orders;


-- Add 7 days to the order_date
SELECT order_id, DATE_ADD(order_date, INTERVAL 7 DAY) AS new_order_date FROM orders;


-- Subtract 1 month from the order_date
SELECT order_id, DATE_SUB(order_date, INTERVAL 1 MONTH) AS previous_month_date FROM orders;


-- Format the order_date to a specific format (MySQL)
SELECT order_id, DATE_FORMAT(order_date, '%Y-%m-%d %H:%i:%s') AS formatted_order_date FROM orders;


-- Format the order_date to a specific format (SQL Server)
SELECT order_id, FORMAT(order_date, 'yyyy-MM-dd HH:mm:ss') AS formatted_order_date FROM orders;


-- Convert a string to a date (MySQL)
SELECT STR_TO_DATE('2023-11-05', '%Y-%m-%d');


-- Find orders placed in October 2023
SELECT * FROM orders WHERE YEAR(order_date) = 2023 AND MONTH(order_date) = 10;


-- Find orders placed last week (assuming Sunday is the first day of the week - Adjust as needed)
SELECT * FROM orders
WHERE order_date BETWEEN CURDATE() - INTERVAL (DAYOFWEEK(CURDATE())+6) DAY AND CURDATE() - INTERVAL (DAYOFWEEK(CURDATE())+0) DAY;


-- Calculate the average shipping time in days
SELECT AVG(DATEDIFF(DAY, order_date, ship_date)) AS average_shipping_time FROM orders;


-- Group orders by month and count the number of orders per month
SELECT YEAR(order_date) AS order_year, MONTH(order_date) AS order_month, COUNT(*) AS order_count
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY order_year, order_month;


-- Example using EXTRACT (PostgreSQL)
SELECT EXTRACT(YEAR FROM order_date) AS order_year, EXTRACT(MONTH FROM order_date) AS order_month FROM orders;


-- Example using CAST to convert a string to a date (SQL Server)
SELECT CAST('2023-11-05' AS DATE);


-- Example using CASE statement to handle different date formats


SELECT
    CASE
        WHEN order_date LIKE '%/%/%' THEN STR_TO_DATE(order_date, '%m/%d/%Y') -- Handle MM/DD/YYYY format
        WHEN order_date LIKE '%-%-%' THEN STR_TO_DATE(order_date, '%Y-%m-%d') -- Handle YYYY-MM-DD format
        ELSE NULL  -- Handle cases where the format is not recognized
    END AS standardized_order_date
FROM
    (SELECT '11/05/2023' AS order_date UNION ALL SELECT '2023-11-06' AS order_date) AS temp_table;

Sample Output (based on the data above):

  • CURRENT_TIMESTAMP: 2023-11-03 14:25:30 (or similar)
  • YEAR(order_date): 2023 for all rows
  • MONTH(order_date): 10 for all rows
  • DATEDIFF(DAY, order_date, ship_date): 2, 2, 2, 2, 2
  • DATE_ADD(order_date, INTERVAL 7 DAY): 2023-11-02 10:00:00, 2023-11-03 12:30:00, etc.
  • DATE_FORMAT(order_date, '%Y-%m-%d %H:%i:%s'): 2023-10-26 10:00:00, 2023-10-27 12:30:00, etc.

6. Common Pitfalls

Section titled “6. Common Pitfalls”
  • Incorrect date format strings: Using incorrect format strings in DATE_FORMAT or STR_TO_DATE can lead to parsing errors or unexpected results. Double-check your format strings against the documentation for your database system.
  • Time zone issues: Forgetting to handle time zones can lead to incorrect calculations and data inconsistencies. Always store dates in UTC and convert to local time zones only for display.
  • Leap year errors: Be aware of leap years when performing date calculations, especially when adding or subtracting months or years.
  • Database-specific syntax: Using functions or syntax that are not supported by your database system will result in errors. Consult the documentation for your database.
  • Incorrect date ranges: When querying for dates within a range, make sure that your BETWEEN clause or comparison operators are correct. For example, to get all records for a specific day, use: WHERE date_column >= '2023-11-03' AND date_column < '2023-11-04'. Using date_column = '2023-11-03' will only match records where the time component is exactly midnight.
  • Null values: Date functions may behave unexpectedly when dealing with NULL values. Use COALESCE or other techniques to handle NULL values appropriately.
  • SQL Injection: Directly embedding user input into date functions can lead to SQL injection vulnerabilities. Always use parameterized queries or prepared statements.
  • Implicit Conversions: Relying on implicit conversions between string and date/time datatypes can lead to unpredictable behavior and errors. Use explicit conversions (e.g., STR_TO_DATE, CAST) to ensure consistency.
  • Performance impact of functions in WHERE clause: Avoid using complex date calculations in the WHERE clause as it can prevent the database from using indexes. If possible, pre-calculate the values or use simpler comparisons.

7. Database Variations

Section titled “7. Database Variations”
FeatureMySQLPostgreSQLSQL ServerOracle
Current Date/TimeNOW(), CURRENT_TIMESTAMP, CURDATE()NOW(), CURRENT_TIMESTAMP, CURRENT_DATEGETDATE(), GETUTCDATE(), SYSDATETIME()SYSDATE, SYSTIMESTAMP
Date ArithmeticDATE_ADD(), DATE_SUB()date + interval '1 day', date - interval '1 month'DATEADD(), DATEDIFF()date + 1, `date - INTERVAL ‘1’ MONTH
Date FormattingDATE_FORMAT()TO_CHAR()FORMAT(), CONVERT()TO_CHAR()
String to DateSTR_TO_DATE()TO_DATE()CONVERT(), TRY_CONVERT()TO_DATE()
Date PartsYEAR(), MONTH(), DAY()EXTRACT(), DATE_PART()YEAR(), MONTH(), DAY()EXTRACT()
Time ZonesCONVERT_TZ()AT TIME ZONESWITCHOFFSET(), TODATETIMEOFFSET()FROM_TZ(), AT TIME ZONE
DATEDIFF UnitTIMESTAMPDIFF(UNIT, date1, date2)date_part('day', date2 - date1)DATEDIFF(UNIT, date1, date2)(date2 - date1) * 24 * 60 * 60 (seconds)

Important Notes:

  • MySQL: DATE_FORMAT uses format specifiers like %Y (year), %m (month), %d (day).
  • PostgreSQL: Uses TO_CHAR for formatting and EXTRACT or DATE_PART for extracting parts. Date arithmetic uses interval literals.
  • SQL Server: FORMAT and CONVERT are used for formatting. DATEADD and DATEDIFF are used for calculations.
  • Oracle: TO_CHAR is used for formatting, TO_DATE for converting strings to dates. Date arithmetic involves adding or subtracting numbers (days). INTERVAL is used for more complex operations.

This cheat sheet provides a comprehensive overview of intermediate-level date and time functions in SQL. Remember to consult the documentation for your specific database system for the most accurate and up-to-date information. Always test your queries thoroughly before deploying them to production.