Date functions

SQL provides various date functions to manipulate and extract information from date and time values. These functions vary slightly across different databases (MySQL, PostgreSQL, SQL Server, Oracle), but the core functionalities remain similar.

1. Getting Current Date and Time

Example Query

SELECT CURRENT_DATE, CURRENT_TIMESTAMP, NOW();

2. Extracting Date Parts

Example Query

SELECT 
    YEAR('2025-01-29') AS Year,
    MONTH('2025-01-29') AS Month,
    DAY('2025-01-29') AS Day;

3. Date Arithmetic (Add/Subtract Days, Months, Years)

Example Query

sqlCopyEditSELECT 
    DATE_ADD('2025-01-29', INTERVAL 10 DAY) AS Plus_10_Days,
    DATE_SUB('2025-01-29', INTERVAL 1 MONTH) AS Minus_1_Month;

4. Date Difference

Example Query

SELECT 
    DATEDIFF('2025-01-29', '2025-01-01') AS Days_Difference;

5. Formatting Dates

Example Query

SELECT 
    DATE_FORMAT('2025-01-29', '%W, %M %d, %Y') AS Formatted_Date; 
-- Output: 'Wednesday, January 29, 2025'

6. Extracting Time

Example Query

SELECT 
    HOUR('2025-01-29 14:30:00') AS Hour,
    MINUTE('2025-01-29 14:30:00') AS Minute;

7. Converting Strings to Dates

Example Query

SELECT STR_TO_DATE('29-01-2025', '%d-%m-%Y') AS Converted_Date;

Conclusion

SQL provides powerful date manipulation functions for:

✅ Getting current date/time
✅ Extracting date parts (YEAR, MONTH, DAY)
✅ Adding/subtracting time intervals
✅ Calculating date differences
✅ Formatting and converting dates