Table of Contents
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.
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 Types||SQL Date Format||Example|
|SmallDateTime||YYYY-MM-DD hh:mm:ss||1979-05-01 12:36:30|
|DateTime||YYYY-MM-DD hh:mm:ss[.nnn]||1979-05-01 12:36:30.123|
|DateTime2||YYYY-MM-DD hh:mm:ss[.nnnnnnn]||1979-05-01 12:36:30.1231231|
|DateTimeOffset||YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm||1979-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
SYSDATETIME() SQL Functions to get the current DateTime value. The SQL Date and Time Functions
SYSUTCDATETIME() functions are used
SELECT GETDATE() as DateTimeFormat1, GETUTCDATE() as DateTimeFormat2, SYSDATETIMEOffset() as DateTimeFormat3, SYSUTCDATETIME() as DateTimeFormat4;
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
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
The syntax of the SQL Server FORMAT function is the following:
- 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:
|tt||AM or PM|
|dddd||Day Name e.g. Monday|
|MMM||Abbreviated Month Name e.g. JAN, FEB|
|MMMM||Full Month Name|
|yyyy||four digit year|
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'.
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';
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
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;
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
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;
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];
Example-6 | Use of DATEADD() with CONVERT() and CAST()
In this example, we will see that
DATADD() SQL Function can be used with
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];
In this article, we learned about SQL Date Formats and how to use
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
- 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