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.

Post Graduate Program In Business Analytics

In partnership with Carlson School of ManagementVIEW COURSE
Post Graduate Program In Business Analytics

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)

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

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

Are you looking for career growth in Business Analysis? Sign-up for our exclusive PGP In Business Analytics course and solve complex business problems quickly!

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!

About the Author

Aryan GuptaAryan Gupta

Aryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.
  • *According to Simplilearn survey conducted and subject to terms & conditions with Ernst & Young LLP (EY) as Process Advisors