
SQL stands for Structured Query Language. It lets you access and manipulate databases. In 1986, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) made SQL a standard. It can insert, delete, alter, and update records in a database. This article will discuss the date format in SQL.
SQL Date Time Format Data Types
The following types of data are available in SQL Server for storing Date or date/time values in the database:
- DATE – format: YYYY-MM-DD
- DATETIME – format: YYYY-MM-DD HH:MI:SS
- TIMESTAMP – format: YYYY-MM-DD HH:MI:SS
- YEAR – format YYYY or YY
Assume that we have the following ‘customers’ table:
Now we will select the records having birth_date of ‘1985-02-07’ from the table above:
Note: The date format you are trying to insert should match the date column format in the database.
Time
- Description: Stores time values (hours, minutes, seconds, and fractional seconds).
- Format: hh:mm:ss[.nnnnnnn]
- Range: 00:00:00.0000000 to 23:59:59.9999999
- Storage Size: 3 to 5 bytes, depending on the precision.
SmallDateTime
- Description: Stores a combination of date and time with lower precision.
- Format: yyyy-mm-dd hh:mm:ss
- Range: 1900-01-01 to 2079-06-06
- Time Precision: Rounded to the nearest minute.
- Storage Size: 4 bytes.
DateTime2
- Description: Stores a combination of date and time with higher precision and a larger range than DateTime.
- Format: yyyy-mm-dd hh:mm:ss[.nnnnnnn]
- Range: 0001-01-01 to 9999-12-31
- Time Precision: Up to 7 digits for fractional seconds.
- Storage Size: 6 to 8 bytes, depending on the precision.
DateTimeOffset
- Description: Stores the date and time along with an offset from UTC.
- Format: yyyy-mm-dd hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]
- Range: 0001-01-01 to 9999-12-31
- Time Precision: Up to 7 digits for fractional seconds.
- Storage Size: 10 bytes.
DATE_FORMAT()
The DATE_FORMAT() functions formats a date as specified.
Syntax: DATE_FORMAT(date,format)
Parameter Values
The table given below will explain the various date formats in SQL.
Format |
Description |
%a |
Abbreviated weekday name (Sun to Sat) |
%b |
Abbreviated month name (Jan to Dec) |
%c |
Numeric month name (0 to 12) |
%d |
Day of the month as a numeric value (01 to 31) |
%e |
Day of the month as a numeric value (0 to 31) |
%f |
Microseconds (000000 to 999999) |
%H |
Hour (00 to 23) |
%h |
Hour (00 to 12) |
%i |
Minutes (00 to 59) |
%j |
Day of the year (001 to 366) |
%k |
Hour (0 to 23) |
%M |
Month name in full (Jan to Dec) |
%m |
Month name as a numeric value (00 to 12) |
%W |
Weekday name in full (Sunday to Saturday) |
%T |
Time in 24 hrs format (hh:mm:ss) |
%U |
A Week where Sunday is the first day of the week (00 to 53) |
SQL Date Format Examples
Example 1:
The above query will convert the birth_date into the specified format.
Example 2:
The above query will convert the birth_date into the specified format.
Example 3:
The above query will convert the birth_date into the specified format.
Conclusion
This brings us to the end of this SQL date format tutorial. We have discussed the formats in which you can enter your date in SQL and how to convert the data into your chosen format. To learn more about SQL, check out our following tutorial on SQL DATEDIFF Function.
This SQL Certification Course gives you all the information you need to start working with SQL databases and use the database in your applications. Learn how to structure your database correctly, author efficient SQL statements and clauses, and manage your SQL database for scalable growth.
FAQs
1. How do I format a date in SQL query?
Use the FORMAT() function to format a date in SQL. Example: SELECT FORMAT(date_column, ‘yyyy-MM-dd’) FROM table_name;.
2. How to get date in dd mm yyyy format in SQL?
Use FORMAT(date_column, ‘dd-MM-yyyy’) or CONVERT(VARCHAR, date_column, 105) for the dd-MM-yyyy format.
3. What are the date formats in SQL TO_DATE?
TO_DATE accepts formats like ‘YYYY-MM-DD’, ‘DD/MM/YYYY’, ‘MM/DD/YYYY’. You define the format with a format mask, e.g., TO_DATE(’01-01-2023′, ‘DD-MM-YYYY’).
4. What is the format of date variable in SQL?
SQL date variables typically use the yyyy-MM-dd format. For example, DATE, DATETIME, or SMALLDATETIME are the defaults in this format.
Source link