SQL stands for Structured Query Language. It lets you access and manipulate databases. SQL became a standard of the American National Standards Institute (ANSI) in 1986 and the International Organization for Standardization (ISO) in 1987. It can insert, delete, alter, 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 format of the date you are trying to insert should match the date column format in the database.
Tip: To keep the queries simple and easy to maintain, do not allow time components in your date.
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.
Earn upto 25 CEUs from Caltech CTME and score a new job with an average annual package of 9-10 L after completing the PGP in Full Stack Web Development. Enroll Today!
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. If you wish to learn more about SQL, then check out our next tutorial on SQL DATEDIFF Function.
This SQL certification course gives you all of the information you need to start working with SQL databases and make use of the database in your applications. Learn how to correctly structure your database, author efficient SQL statements, and clauses, and manage your SQL database for scalable growth.
If you have any questions, please feel free to ask them in our comments section, and our experts will answer them at the earliest!