SQL Tutorial

SQL Clauses / Operators

SQL-Injection

SQL Functions

SQL Queries

PL/SQL

MySQL

SQL Server

Misc

SQL | Date functions

SQL provides a variety of functions to manipulate and extract components from date and time values. The specific functions and their behaviors can vary slightly among different relational database management systems (RDBMS), but many are standardized across platforms.

Here's a list of commonly used SQL date functions:

1. Date Extraction:

DAY(date): Extracts the day as an integer from a date.
MONTH(date): Extracts the month as an integer from a date.
YEAR(date): Extracts the year as an integer from a date.
HOUR(time), MINUTE(time), SECOND(time): Extract the respective time components from a time or timestamp value.

2. Date Addition and Subtraction:

DATE_ADD(date, INTERVAL expr unit) and DATE_SUB(date, INTERVAL expr unit): Adds or subtracts a specified time interval from a date. The specific syntax might vary. For example, in MySQL, you can use these to add 3 days: DATE_ADD(date, INTERVAL 3 DAY).

3. Date Difference:

DATEDIFF(date1, date2): Returns the difference between two dates. The specific interpretation (like which date is subtracted from which) can vary between RDBMSs.

4. Current Date and Time:

CURRENT_DATE: Returns the current date.
CURRENT_TIME: Returns the current time.
CURRENT_TIMESTAMP or NOW(): Returns the current date and time.

5. Formatting:

DATE_FORMAT(date, format): Converts a date to a string using the specified format. This function is notably used in MySQL.

6. Parsing:

STR_TO_DATE(string, format): Parses a string into a date using the specified format. This is notably a MySQL function.
TO_DATE(string, format): Similar to STR_TO_DATE, but used in Oracle and some other RDBMSs.

7. Date Arithmetic:

DATEPART(part, date): Extracts a specific part of the date in some databases, like SQL Server.
DATE_TRUNC('field', source_timestamp): Truncates the date to the specified field. This is a PostgreSQL function.

8. Other Useful Functions:

DAYOFWEEK(date): Returns an integer representing the day of the week.
LAST_DAY(date): Returns the last day of the month for the specified date. This is useful in databases like Oracle.
EXTRACT(field FROM source_date): A more generalized function to extract a component (like day, month, year, etc.) from a date. Supported in databases like PostgreSQL and Oracle.
AGE(timestamp, timestamp): In PostgreSQL, this function returns the age between two timestamps.

Again, it's crucial to refer to the official documentation of your specific RDBMS to understand the exact syntax and behavior of these functions. Different databases might have slight variations or additional functions that are not universally supported.

How to Use Date Functions in SQL:
- Example:
```
SELECT GETDATE();
```

Extracting Parts of a Date with SQL Functions:

Example:

SELECT YEAR(order_date), MONTH(order_date), DAY(order_date)
FROM orders;

Formatting Dates in SQL with Date Functions:

Example:

SELECT FORMAT(order_date, 'YYYY-MM-DD') AS formatted_date
FROM orders;

Calculating the Difference Between Two Dates in SQL:

Example:

SELECT DATEDIFF(day, start_date, end_date) AS day_difference
FROM events;

Adding and Subtracting Days with SQL Date Functions:

Example:

SELECT DATEADD(day, 7, order_date) AS new_delivery_date
FROM orders;

Using GETDATE() and CURRENT_DATE() in SQL:

Example:

SELECT GETDATE() AS current_datetime, CURRENT_DATE() AS current_date;

Date Functions for Working with Timestamps in SQL:

Example:

SELECT TIMESTAMPDIFF(SECOND, start_timestamp, end_timestamp) AS duration_seconds
FROM log_entries;

Date Functions for Manipulating Time in SQL:

Example:

SELECT DATEADD(hour, 3, order_time) AS updated_order_time
FROM orders;

Handling NULL Values with Date Functions in SQL:

Example:

SELECT COALESCE(order_date, 'No Date') AS order_date
FROM orders;

Performing Date Calculations with SQL Functions:

Example:

SELECT DATEADD(month, -3, GETDATE()) AS three_months_ago;

Using Date Functions in WHERE Clauses in SQL:

Example:

SELECT *
FROM orders
WHERE order_date >= '2022-01-01';

Aggregate Functions with Date Columns in SQL:

Example:

SELECT AVG(DATEDIFF(day, order_date, delivery_date)) AS avg_delivery_time
FROM orders;