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.

Want a Top Software Development Job? Start Here!

Full Stack Development-MEANExplore Program
Want a Top Software Development Job? Start Here!

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:

data

Now we will select the records having birth_date of ‘1985-02-07’ from the table above:

date_format-in-SQL

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)

Preparing Your Blockchain Career for 2024

Free Webinar | 5 Dec, Tuesday | 9 PM ISTRegister Now
Preparing Your Blockchain Career for 2024

SQL Date Format Examples

Example 1:

date-format-in-SQL-example

The above query will convert the birth_date into the specified format.

output-date-format-in-SQL

Example 2:

date-format-2

The above query will convert the birth_date into the specified format.

output-format-2

Example 3:

format-3

The above query will convert the birth_date into the specified format.

output-format-3

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!

Our Software Development Courses Duration And Fees

Software Development Course typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Caltech Coding Bootcamp

Cohort Starts: 15 Apr, 2024

6 Months$ 8,000
Automation Test Engineer

Cohort Starts: 28 Feb, 2024

11 Months$ 1,750
Full Stack Java Developer

Cohort Starts: 19 Mar, 2024

6 Months$ 2,000
Full Stack Developer Course - MERN Stack

Cohort Starts: 3 Apr, 2024

6 Months$ 1,500