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.

Advertisement

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.

Data TypesSQL Date FormatExample
Timehh:mm:ss[.nnnnnnn]12:36:30.1231231
DateYYYY-MM-DD1979-05-01
SmallDateTimeYYYY-MM-DD hh:mm:ss1979-05-01 12:36:30
DateTimeYYYY-MM-DD hh:mm:ss[.nnn]1979-05-01 12:36:30.123
DateTime2YYYY-MM-DD hh:mm:ss[.nnnnnnn]1979-05-01 12:36:30.1231231
DateTimeOffset

YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm1979-05-01 12:36:30.1231231 + 05:30

 

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

SQL Date Formats in MS SQL SERVER - Convert and Format
SQL Date Formats in SQL SERVER - Sample output 1

 

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.

Advertisement

 

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:

FormatRange/Output
dd01-31
MM01-12
yy00-99
HH00-23
mm00-59
ss00-59
ttAM or PM
ddddDay Name e.g. Monday
MMMAbbreviated Month Name e.g. JAN, FEB
MMMMFull Month Name
yyyyfour digit year
d1-31
udUS Culture

 

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 FORMATS with FORMAT() Function
SQL SERVER DATE FORMATS with FORMAT() Function - Sample Output 2

 

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. 

Advertisement

 

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

SQL SERVER DATE FORMATS with FORMAT() and Culture argument
Use of FORMAT() Function with culture - Sample output 3

 

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 expressionstyle 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.

SQL Date format Styles in SQL Server
SQL Date format Styles in SQL Server | Figure 1

 

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

SQL Date Foramt with CONVERT() Functions - Output
SQL Date Format with CONVERT() - Sample Output 4

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

SQL Date Format with CAST() Function - Sample output 5
SQL Date Format with CAST() Function - Sample output 5

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

sql date - Date_Format() example 5
SQL Date Format | Example 5 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

SQL DATE Convert Foramt with DATEADD Example 6
SQL DATE Convert Format with DATEADD | Example 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

 

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment