Meritshot Tutorials

  1. Home
  2. »
  3. SQL Date Functions
SQL

SQL Tutorial

SQL Date Functions

SQL date functions are used to perform operations on date and time values. They help in retrieving, manipulating, and formatting date and time data in SQL queries. Different database systems may have their own set of date functions, but many of these functions are common across major SQL databases.

Common SQL Date Functions

1 GETDATE() / CURRENT_DATE / NOW()

Returns the current date and time.

  • SQL Server: GETDATE()
  • MySQL: NOW()
  • PostgreSQL: CURRENT_TIMESTAMP
  • Oracle: SYSDATE

— SQL Server

SELECT GETDATE(); — Example output: 2024-08-29 10:15:30.123

— MySQL

SELECT NOW(); — Example output: 2024-08-29 10:15:30

— PostgreSQL

SELECT CURRENT_TIMESTAMP; — Example output: 2024-08-29 10:15:30

— Oracle

SELECT SYSDATE FROM dual; — Example output: 29-AUG-2024

2 DATEADD() / ADDDATE() / INTERVAL

Adds a specified time interval to a date.

  • SQL Server: DATEADD(datepart, number, date)
  • MySQL: ADDDATE(date, INTERVAL expr unit)
  • PostgreSQL: date + interval ‘value unit

— SQL Server

SELECT DATEADD(day, 10, GETDATE()); — Adds 10 days to the current date

— MySQL

SELECT ADDDATE(NOW(), INTERVAL 10 DAY); — Adds 10 days to the current dat

— PostgreSQL

SELECT CURRENT_DATE + INTERVAL ’10 days’; — Adds 10 days to the current date

3 DATEDIFF() / TIMESTAMPDIFF() / AGE()

Calculates the difference between two dates.

  • SQL Server: DATEDIFF(datepart, startdate, enddate)
  • MySQL: TIMESTAMPDIFF(unit, datetime1, datetime2)
  • PostgreSQL: AGE(timestamp1, timestamp2)

— SQL Server

SELECT DATEDIFF(day, ‘2024-08-01’, GETDATE()); — Difference in days

— MySQL

SELECT TIMESTAMPDIFF(DAY, ‘2024-08-01’, NOW()); — Difference in days

— PostgreSQL

SELECT AGE(CURRENT_DATE, ‘2024-08-01’); — Difference in years, months, days

4 DATEPART() / DAY() / EXTRACT()

Extracts specific parts of a date.

  • SQL Server: DATEPART(datepart, date)
  • MySQL: DAY(date) (for day of the month)
  • PostgreSQL: EXTRACT(field FROM date)

— SQL Server

SELECT DATEPART(year, GETDATE()); — Extracts the year

— MySQL

SELECT DAY(NOW()); — Extracts the day of the month

— PostgreSQL

SELECT EXTRACT(month FROM CURRENT_DATE); — Extracts the month

5 FORMAT() / TO_CHAR() / TO_DATE()

Formats a date into a specific string format.

  • SQL Server: FORMAT(date, format_string)
  • MySQL: DATE_FORMAT(date, format_string)
  • PostgreSQL: TO_CHAR(date, format_string)
  • Oracle: TO_CHAR(date, format_string)

— SQL Server

SELECT FORMAT(GETDATE(), ‘dd-MM-yyyy’); — Formats as ’29-08-2024′

— MySQL

SELECT DATE_FORMAT(NOW(), ‘%d-%m-%Y’); — Formats as ’29-08-2024′

— PostgreSQL

SELECT TO_CHAR(CURRENT_DATE, ‘DD-MM-YYYY’); — Formats as ’29-08-2024′

— Oracle

SELECT TO_CHAR(SYSDATE, ‘DD-MON-YYYY’) FROM dual; — Formats as ’29-AUG-2024′

Example:

1 Calculating a Future Date

Add 30 days to the current date:

— SQL Server

SELECT DATEADD(day, 30, GETDATE());

— MySQL

SELECT ADDDATE(NOW(), INTERVAL 30 DAY);

— PostgreSQL

SELECT CURRENT_DATE + INTERVAL ’30 days’;

2 Finding Age from a Birthdate

Calculate age from a birthdate:

— SQL Server

SELECT DATEDIFF(year, ‘1990-05-15’, GETDATE()) AS Age;

— MySQL

SELECT TIMESTAMPDIFF(YEAR, ‘1990-05-15’, NOW()) AS Age;

— PostgreSQL

SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, ‘1990-05-15’)) AS Age;

3 Formatting Dates for Display

Format the current date in a specific format:

— SQL Server

SELECT FORMAT(GETDATE(), ‘MMMM dd, yyyy’); — Example output: ‘August 29, 2024’

— MySQL

SELECT DATE_FORMAT(NOW(), ‘%M %d, %Y’); — Example output: ‘August 29, 2024’

— PostgreSQL

SELECT TO_CHAR(CURRENT_DATE, ‘Month DD, YYYY’); — Example output: ‘August 29, 2024’

Tips to Remember

  1. Know Your Database: Different SQL databases have variations in date functions, so check the specific syntax and functions for your database system.
  2. Handle Time Zones: Be aware of time zone considerations when working with date and time data, especially for applications dealing with multiple time zones.
  3. Format Consistently: Ensure date formats are consistent with your application requirements to avoid confusion and errors.

Frequently Asked Questions

Q1: Can I perform arithmetic operations on date columns?
A1: Yes, you can use date functions to add or subtract time intervals, and calculate differences between dates.

Q2: What if my database does not support a specific date function?
A2: Consult the documentation for your database system to find equivalent functions or workarounds.

Q3: Can date functions handle date and time values together?
A3: Yes, many date functions work with both date and time values, allowing for operations on timestamps.

Q4: How do I deal with different date formats in SQL?
A4: Use format functions like FORMAT() or TO_CHAR() to standardize date formats for consistency.

Q5: Can I compare dates directly in SQL queries?
A5: Yes, you can compare date values directly in queries, but ensure the formats are compatible and handle any time zone differences.

❮ Previous
Next ❯
ads website