SQL Date Format Guide: Learn How to Format Dates 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.

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.

Discover SQL and Database Management

SQL Certification CourseExplore Program

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.