SQL Date & Time Functions: Level Up Your SQL Skills

SQL provides a variety of date and time functions that allow you to manipulate and perform operations on date and time values. Here are some commonly used date and time functions to level up your SQL skills:

CURRENT_DATE:

• Returns the current date.

SELECT CURRENT_DATE;

CURRENT_TIME:

• Returns the current time.

SELECT CURRENT_TIME;

CURRENT_TIMESTAMP:

• Returns the current date and time.

SELECT CURRENT_TIMESTAMP;

EXTRACT:

• Extracts a specific part of a date or time, such as year, month, day, hour, minute, or second.

SELECT EXTRACT(YEAR FROM hire_date) AS hire_year FROM employees;

DATE_PART:

• Similar to EXTRACT, it retrieves a specific part of a date or time.

SELECT DATE_PART('month', hire_date) AS hire_month FROM employees;

DATE_ADD / DATE_SUB:

• Adds or subtracts a specific interval to/from a date.

SELECT DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY) AS next_week;

DATEDIFF / TIMESTAMPDIFF:

• Calculates the difference between two dates or times.

SELECT DATEDIFF('2024-02-25', '2024-02-20') AS date_difference;

DATE_FORMAT:

• Formats a date or time value as a string.

SELECT DATE_FORMAT(hire_date, '%Y-%m-%d') AS formatted_date FROM employees;

DATE_TRUNC:

• Truncates a date or time value to a specified level of precision

SELECT DATE_TRUNC('month', hire_date) AS truncated_month FROM employees;

AGE:

• Calculates the age based on a birthdate.

SELECT AGE('1990-05-15') AS age;