SQL Date Format Explained with Examples

 

SQL Date Format - Introduction

In this article, you will learn how to format date and time values using SQL Statements. There are more than 50 SQL Date Formats that can be used in different versions of SQL Server, MySQL, and other database management systems. Apart from SQL Date Formats, Wikipedia provides a lengthy list of SQL date formats used in different parts of the world. For example in the United States, a unique date format 'mm-dd-yyyy' is being used. Some nations, such as Korea, Iran, and China, write the year first and the day last (yyyy-mm-dd). (MIT)

This different Date interpretation varies between different countries. Suppose you have stored the '01-05-1979' date in your SQL database. SQL Server and MySQL have their specific Date formats. On retrieval of this date can communicate different meanings to different peoples across the globe.

Hence for a database programmer and a user, SQL Date Format is very much necessary to learn. In this article, we will try to enlist and outline the details of all SQL Date Formats used in SQL Server and MySQL with east and practical examples.

As you have already learned, various SQL Date Functions and SQL Time Functions articles about the different functions like FORMAT() and CONVERT() can be used to format Date in a specific way.

Types of MS SQL Server Date Formats

MS SQL Server provides Six different data types for manipulating Date and Time values.  Similarly, you can SQL Server Convert Date Formats.

 

Let run this SELECT statement on MS SQL SEVER to verify the above SQL Date formats in SQL SERVER. In this query, we have used GETDATE() and SYSDATETIME() SQL Functions to get the current DateTime value. The SQL Date and Time Functions GETUTCDATE() and SYSUTCDATETIME() functions are used

SELECT GETDATE() as DateTimeFormat1,
       GETUTCDATE() as DateTimeFormat2,
       SYSDATETIMEOffset() as DateTimeFormat3,
       SYSUTCDATETIME() as DateTimeFormat4;

SAMPLE OUTPUT

 

Use of FORMAT() Function in SQL Date Formats

Let's start explaining the use of FORMAT() Microsoft SQL Server Function for formatting the dates and times. Before MS SQL SERVER 2008 version, the CONVERT() Function was to perform all SQL Date Formats and to convert from one Date Format to other. We will also explain the use of CAST() and CONVERT() for SQL Date convert and format options in this article. After learning the syntax, you will be demonstrated a list of the available SQL examples on how to use the CONVERT() SQL Server function to handle different SQL SERVER Date formats in a database.

 

Syntax of FORMAT() Function

In, MS SQL SERVER 2012, the FORMAT() function is introduced for handling the conversion of SQL Date Formats. You do not need to remember the default SQL DateTime Format Numbers to convert a date from one specific format to another in the FORMAT() function.

The syntax of the SQL Server FORMAT function is the following:

FORMAT (value,format[,culture])

Here,

• value: must be an expression or value that belongs to any supported DateTime data type.
• format: can be a nvarchar format pattern containing a valid format string. It can be a customer pattern supported characters for dates and numeric values.
• culture: An optional nvarchar argument specifying a culture. If the culture argument is not provided, the language of the current session is used. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL Server. If the culture argument is not valid, FORMAT raises an error. For getting the list of culture, you can visit Wikipedia for ISO 639-1, ISO 3166-1 alpha-2.

 

Different types of supported date formats

Here is the table which shows the list of supported dates characters with their range and expected output values:

 

Example-1 | SQL Date Format with the FORMAT() Function

Now is the perfect time to work with SQL Date formats examples with the FORMAT() functions. In all the below examples, We will use Use the FORMAT() SQL Function to mentioning Different SQL Date Formats. We will use the GETDATE() function to get the DateTime value and will use CUSTOM Format String. In this SELECT Statement, we will use only two arguments (value, and format_string).

DECLARE @datetimevalue DATETIME = '01/05/1979';
SELECT FORMAT (@datetimevalue, 'dd-MM-yy') as 'dd-mm-yy',
       FORMAT (@datetimevalue, 'dd-MM-yy') as 'dd-mm-yyyy',
       FORMAT (@datetimevalue, 'mm-dd-yy') as 'mm-dd-yy',
       FORMAT (@datetimevalue, 'mm-dd-yyyy') as 'mm-dd-yyyy',
       FORMAT (@datetimevalue, 'dd/MM/yy') as 'dd/mm/yy',
       FORMAT (@datetimevalue, 'dd/MM/yy') as 'dd/mm/yyyy';

In this SELECT Query to convert and format the DECLARE @datetimevalue '01/05/1979', we have used a Custom Format string. You can see that no SQL Date Format number is being used as it was being used in the CONVERT() SQL DATE Format Function. Six different format_strings have been mentioned in every FORMAT() function with same the @datetimevalue = '01/05/1979'.

SAMPLE OUTPUT

 

SQL Server Date FORMAT with Culture

The Culture is an optional argument that can be used for SQL Date convert and format. With the culture argument, we can get SQL Date Formats for different regions. As Format() function is very rich in syntax and arguments and can be used with all data types supported by .NET and SQL Server. Hence we have a lengthy list of culture codes to use with FORMAT SQL Date Format and Convert function. 

 

Example-2 | Use of Culture with SQL Date Format()

Here is another example of the SELECT Statement that will use the culture argument in the FORMAT() function. You will see how simple it will be to get the required SQL Date Format for a specific region. For example, you want to convert the DateTime value into US English, Norwegian, Zulu, and Indian-Hindi format. We will use only their culture code from the above-mentioned list as under

DECLARE @datetimevalue DATETIME = '05/01/1979';  
SELECT FORMAT (@datetimevalue, 'dddd,d, MMMM, yyyy', 'en-us') AS 'US English Result',  
       FORMAT (@datetimevalue, 'd', 'no') AS 'Norwegian Result',  
       FORMAT (@datetimevalue, 'D', 'zu') AS 'Zulu Result',
       FORMAT (@datetimevalue, 'dddd,d, MMMM,yyyy', 'hi-IN') AS 'Indian Result';

SAMPLE OUTPUT

 

CAST and CONVERT() Date Format Function

Microsoft SQL Server 2008 and earlier versions used this CONVERT() SQL function to format dates in SQL queries and other SQL procedures. These functions convert an expression of one data type to another. Hence these SQL functions can also be used to Format SQL Dates. The CAST() and The CONVERT() function can convert a value (of any type) into a specified datatype.

As far as SQL Date Formatting is concerned, the FORMAT() function is a much better choice as compared to the CONVERT() SQL function. It is not very flexible and provides limited date formats and styles in contrast to the SQL FORMAT() function for date formatting.

 

SQL Server Date and Time styles

SQL Date Formats/styles are mentioned in the official documentation with the help of a table consisting of more than 40 different date formats. For a date or time data type expression, style can have one of the values shown in the following table. It is not be mentioned here that CAST() SQL Function can convert/format a SQL date format to any other datatype but cannot use these styles.

 

Syntax of CAST() / CONVERT()

CAST(expression AS datatype(length))
CONVERT(data_type(length), expression, style)

In both these syntax, the expression means the data type which you need to format or convert a SQL DateTime value. You can observe in both The CAST() and CONVERT() expression and data_type(length) parameters are being indifferent sequences. The CONVERT SQL Function contains an extra parameter style which can be chosen from the above-mentioned table from the official documentation.

Here is a smaller snapshot of this table.

 

Example-3 | Use of CONVERT() for SQL Server DATE Formats

In this code you can see that we are using two styles USA and ANSI in the CONVERT() SQL Server Function with the help of Style parameter values 101 and 102 respectively. We have used the VARCHAR data type to convert the values returned by the GETDATE() SQL Function. As shown in the above Figure 1 showing the style table, USA date style code 101 for displaying year value with century and only 1 for year value without century.

SELECT CONVERT(varchar(100),GETDATE(),1) as USA_no_century,
       CONVERT(varchar(100),GETDATE(),101) as USA_wd_centtury,
       CONVERT(varchar(100),GETDATE(),2) as ANSI_no_century,
       CONVERT(varchar(100),GETDATE(),102) as ANSI_wd_centtury;

SAMPLE OUTPUT

In the output, you can see the style values from figure 1 are correctly producing the output. In these, all examples USA style value is used in different ways with Date and Datetime target format.

 

Example-4 | Use of CAST() for SQL Server DATE Formats

The CAST() SQL Function is used to convert a data type into another data type. Here in this example, we will use the CAST() SQL Function to convert a DateTime value returned by GETDATE() function into varchar, DateTime, date, and time data types.

SELECT CAST(GETDATE()as varchar(40)) as DateAsVarchar,
       CAST(GETDATE()as datetime) as DateAsDateTime,
       CAST(GETDATE()as date) as DateAsDate,
       CAST(GETDATE()as time) as DateAsTime;

SAMPLE OUTPUT

In the sample output of this example, you can see that

• The first column GETDATE() value is converted to varchar
• Second column GETDATE() value is converted into DateTime
• Third column GETDATE() value is converted in only the Date
• Fourth column GETDATE() value is converted in only the Time

 

Example-5 | Use of Long SQL Date Formats with CONVERT()

In most parts of the world, a long-date format is being used. For example in Europe, the Following "DD MMM YYYY HH:MM:SS: MMM"  long date format is used. In the United States, this "USA with Time AM/PM" long date format is also used. ISO uses this long-date format  "YYYY-MM-DDTHH:MM: SS.mmm".

In this example, we will use see how the CONVERT() function can be used with Style code from the official SQL Date FORMAT Styles table to format SQL dates in the above-mentioned date formats.

Declare @theDate datetime
SET @theDate=GETDATE()
SELECT  CONVERT(varchar,@theDate,9) as [MMM DD YYYY hh:mm:ss:mmm(AM/PM)],
        CONVERT(varchar,@theDate,13) as [DD MMM YYYY HH:MM:SS:MMM],
        CONVERT(varchar,@theDate,21) as [YYYY-MM-DD HH:MM:SS.mmm],
        CONVERT(varchar,@theDate,22) as [USA with Time AM/PM],
        CONVERT(varchar,@theDate,22) as [Islamic/Hijri_date];

SAMPLE OUTPUT

 

Example-6 | Use of DATEADD() with CONVERT() and CAST()

In this example, we will see that DATADD() SQL Function can be used with CAST() and CONVERT() date functions to add and subtract date with new formats. As you know that, DATEADD() SQL Server function can add/subtract year, month, or day values from the given DateTime value. In this code, we will get the current date using the GETDATE() Function and then will add and subject 1 year. We will use short date format 107 from the style table.

SELECT Cast(GETDATE() AS Date) AS [NewDate],
       CONVERT(varchar(110),DATEADD(YEAR,1,GETDATE()),107) AS [AddedConvertedDate],
       CONVERT(varchar(110),DATEADD(YEAR,-1,GETDATE()),107) AS [SubConvertedDate];
 

SAMPLE OUTPUT

 

Summary

In this article, we learned about SQL Date Formats and how to use FORMAT() , CAST() and CONVERT() SQL Functions. Different date formats have been reported by Wikipedia in different parts of the world. Hence, SQL Server and other DBMS have made it possible to covert and format the SQL Date using different built-in SQL Date Format and CONVERT Functions.

In earlier versions of Microsoft SQL SEVER, CONVERT() SQL Function was used with a pre-defined list of SQL Date Format and styles. In, MS SQL SERVER 2012 and higher, the FORMAT() function is introduced for handling the conversion and formatting of SQL Date Formats. You do not need to remember the default SQL DateTime Format Numbers to convert a date from one specific format to another in the FORMAT() function.

 

References

• Date and Time Data Types and Functions
• SQL CAST and CONVERT
• Various Ways to Use the SQL CONVERT Date Function
• SQL Custom date and time format strings