WikiGalaxy
SQL Dates
DATE Data Type:
The DATE data type in SQL is used to store date values. It stores the year, month, and day in a format like YYYY-MM-DD.
CURRENT_DATE Function:
CURRENT_DATE function returns the current date of the system. It’s useful in queries where you need to compare or calculate based on the current date.
Extracting Year from Date:
To extract the year from a date, you can use the EXTRACT function. This allows you to isolate specific parts of a date for analysis.
Adding Days to a Date:
You can add days to a date using the DATE_ADD function. This is useful for calculating future dates from a given start point.
Date Difference Calculation:
DATEDIFF function calculates the difference in days between two dates. It’s helpful in determining durations or intervals.
Formatting Dates:
The DATE_FORMAT function allows you to format a date into different styles, such as 'MM/DD/YYYY', which is often required for reports.
Converting Strings to Dates:
STR_TO_DATE function converts a string into a date type. It’s crucial when dealing with data that comes in string format.
Handling Time Zones:
Using CONVERT_TZ function, you can convert dates between different time zones, which is essential for applications that operate across regions.
-- Example 1: Using DATE Data Type
CREATE TABLE events (
event_id INT PRIMARY KEY,
event_date DATE
);
-- Example 2: Getting the Current Date
SELECT CURRENT_DATE;
-- Example 3: Extracting Year from a Date
SELECT EXTRACT(YEAR FROM '2023-10-25') AS year;
-- Example 4: Adding Days to a Date
SELECT DATE_ADD('2023-10-25', INTERVAL 10 DAY) AS new_date;
-- Example 5: Calculating Date Difference
SELECT DATEDIFF('2023-11-05', '2023-10-25') AS days_difference;
-- Example 6: Formatting Dates
SELECT DATE_FORMAT('2023-10-25', '%m/%d/%Y') AS formatted_date;
-- Example 7: Converting Strings to Dates
SELECT STR_TO_DATE('25-10-2023', '%d-%m-%Y') AS converted_date;
-- Example 8: Handling Time Zones
SELECT CONVERT_TZ('2023-10-25 15:00:00', 'UTC', 'America/New_York') AS new_time;
DATE Data Type:
The DATE data type is fundamental for storing date values in SQL databases, allowing for efficient date-based operations and queries.
CURRENT_DATE Function:
Using CURRENT_DATE ensures that your queries are always working with the most up-to-date information without manual input.
Extracting Year from Date:
EXTRACT function simplifies the process of analyzing date components, making it easier to group or filter data by year.
Adding Days to a Date:
DATE_ADD is particularly useful in scheduling applications where future dates need to be calculated dynamically.
Date Difference Calculation:
DATEDIFF helps in understanding time gaps between events, which is crucial for reporting and analytics.
Formatting Dates:
DATE_FORMAT adapts date outputs to match user preferences or regional standards, improving readability and usability.
Converting Strings to Dates:
STR_TO_DATE ensures that imported data is correctly interpreted as dates, preventing errors in data processing.
Handling Time Zones:
CONVERT_TZ provides flexibility in managing global operations, ensuring that date and time data is accurate across different time zones.
Console Output:
2023-10-25
10/25/2023
2023
2023-11-04
11
25/10/2023
2023-10-25 11:00:00
