What is SQL Date Format and How to Change It?

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.

In this tutorial, we will discuss the following topics:

  • SQL date data types
  • SQL DATE_FORMAT()
  • SQL Date Format Examples

SQL Date Data Types

SQL comes with the following data types for storing the date/time value 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. 

Business Analyst Master's Program

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

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:

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

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

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.