In SQL, the DATE data type is used to store date values. Dates are one of the most commonly used data types in SQL and are used to record events, transactions, and other time-related information. In this article, we will look at the basics of working with dates in SQL.
Inserting Dates
To insert a date into a database, you can simply provide the date in the format ‘YYYY-MM-DD’. For example:
INSERT INTO table_name (column_name)
VALUES ('2022-06-01');
Selecting Dates
To select dates from a database, you can use the SELECT statement as follows:
SELECT column_name FROM table_name;
The result of this query will be a table containing the values stored in the ‘column_name’ column.
Comparing Dates
To compare dates in SQL, you can use comparison operators such as ‘<‘, ‘>’, ‘<=’, ‘>=’, and ‘=’. For example, to select all records where the date in the ‘column_name’ column is greater than ‘2022-06-01’, you would use the following SQL query:
SELECT column_name FROM table_name WHERE column_name > '2022-06-01';
Date Arithmetic
In SQL, you can perform arithmetic operations on dates. For example, to add 5 days to the date ‘2022-06-01’, you would use the following SQL query:
SELECT '2022-06-01' + INTERVAL 5 DAY;
The result of this query would be ‘2022-06-06’. Similarly, you can subtract dates or add/subtract intervals to/from dates. The INTERVAL keyword is used to specify the interval to add or subtract.
Working with Date and Time
In SQL, the DATETIME data type is used to store both the date and time values. To insert a date and time into a database, you can provide the value in the format ‘YYYY-MM-DD HH:MM:SS’. For example:
INSERT INTO table_name (column_name)
VALUES ('2022-06-01 10:30:00');
In SQL, you can also extract the time portion of a DATETIME value using the TIME() function. For example:
SELECT TIME('2022-06-01 10:30:00');
The result of this query would be ’10:30:00′.
Extracting Parts of a Date
To extract parts of a date in SQL, you can use the following functions:
- YEAR(): Returns the year of the date.
- MONTH(): Returns the month of the date.
- DAY(): Returns the day of the date.
- HOUR(): Returns the hour of the date.
- MINUTE(): Returns the minute of the date.
- SECOND(): Returns the second of the date.
For example, to extract the year, month, and day from the date ‘2022-06-01’, you would use the following SQL query:
SELECT YEAR('2022-06-01') AS year,
MONTH('2022-06-01') AS month,
DAY('2022-06-01') AS day;
The output of this query would be:
year | month | day -----|-------|---- 2022 | 06 | 01
Formatting a Date
To format a date in a specific way, you can use the DATE_FORMAT() function in SQL. The DATE_FORMAT() function takes two arguments: the date and the format string. The format string defines the desired output format of the date.
For example, to format the date ‘2022-06-01’ as ‘June 01, 2022’, you would use the following SQL query:
SELECT DATE_FORMAT('2022-06-01', '%M %d, %Y') AS formatted_date;
The output of this query would be:
formatted_date --------------- June 01, 2022
In the format string, the following placeholders can be used:
- %Y: Year with century, as a decimal number.
- %y: Year without century, as a decimal number.
- %M: Month name, based on the locale.
- %m: Month number (01-12).
- %D: Short name of the day of the week.
- %d: Day of the month (00-31).
- %H: Hour (00-23).
- %i: Minutes (00-59).
- %s: Seconds (00-59).
The placeholders can be used in any order to format the date as desired. The DATE_FORMAT() function is useful when displaying dates to the user, as it allows you to control the output format of the date.
Converting Strings to Dates
In some cases, you may have date values stored as strings in your database. To convert a string to a date, you can use the STR_TO_DATE() function in SQL. The STR_TO_DATE() function takes two arguments: the string to be converted and the format of the string. For example, to convert the string ’01-Jun-22′ to a date, you would use the following SQL query:
SELECT STR_TO_DATE('01-Jun-22', '%d-%M-%y') AS converted_date;
The output of this query would be:
converted_date --------------- 2022-06-01
The format of the string argument in the STR_TO_DATE() function must match the format of the date string, so that the function can correctly parse the string and convert it to a date.
Conclusion
In conclusion, the SQL DATE data type is a crucial tool for working with date values in SQL. This article covered various aspects of working with dates in SQL including inserting dates, selecting dates, comparing dates, date arithmetic, working with date and time, extracting parts of a date, and formatting a date. By using the techniques outlined in this article, you can easily manipulate and format dates in SQL for your data management needs.
