SQL server date format and converting it (Various examples)

When we are working with date time in SQL server, we may not get the format which we want in our application, so in that case, we would have to convert that basic SQL Server date format into the one we need, as every geographic location has different need of the date formats.

SQL server we have various ways to convert date time into the one we need, let's take a look on each one of them.

SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, then getutcdate() should be used.

Let's try using above functions getdate() & getutcdate() to check what the output of the these functions, you can navigate to your SQL server management studio, and click on the new query

As you can see in the above-executed query we have got local datetime using getdate(), Current UTC time using getUTCdate() & using SYSDATETIME() function returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.

Converting Datetime to Date using CONVERT()

Now, we know how to get datetime of the system using the above queries, but we would have to convert it into specific format let's try to get the only date from the datetime using CONVERT() function of SQL Server but before that here is the Syntax of Convert() function in SQL server

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )  

expression
Any valid expression.

data_type
The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.

length
An optional integer that specifies the length of the target data type. The default value is 30.

style
An integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.

SELECT CONVERT (date, SYSDATETIME())  
    ,CONVERT (date, SYSDATETIMEOFFSET())  
    ,CONVERT (date, SYSUTCDATETIME())  
    ,CONVERT (date, CURRENT_TIMESTAMP)  
    ,CONVERT (date, GETDATE())  
    ,CONVERT (date, GETUTCDATE()); 

Executing the above query in SQL server gives

As you can see in the above image, we have got extracted only date using various default datetime functions available for SQL server 2008 & above. 

Extracting date from datetime in a specific format using CONVERT()

Now, we understand how to get only date from the datetime, but how to get specific date format like dd/MM/yyyy or dd-mm-yyyy or in any other format, so we can use CONVERT function to get the date in the specific format and here is the reference table which provides us the code of format's

Here are some of the codes used in sample query

SELECT CONVERT(varchar, SYSDATETIME(), 100) as Default_Format,
       CONVERT(varchar, SYSDATETIME(), 1) AS [USA format 1],  
	   CONVERT(varchar, SYSDATETIME(), 101) AS [USA format 2],
	   CONVERT(varchar, SYSDATETIME(), 2) AS [YY.MM.DD ANSI format],
	   CONVERT(varchar, SYSDATETIME(), 3) AS [DD/MM/YY Biritish format],
	   CONVERT(varchar, SYSDATETIME(), 4) AS [DD.MM.YY German format],
	   CONVERT(varchar, SYSDATETIME(), 113) AS [Europe Format],
	   CONVERT(varchar, SYSDATETIME(), 120) as ODBC_cannonical

When executed in the above query in SQL server, here is the output

You can also get all available Date formatting code available by executing the below query

DECLARE @counter INT = 0
DECLARE @date DATETIME = '2018-06-23 19:38:54.840'

CREATE TABLE #dateFormatsAvailable (dateFormatCode int, dateOutput varchar(40))

WHILE (@counter <= 150 )
BEGIN
   BEGIN TRY
      INSERT INTO #dateFormatsAvailable
      SELECT CONVERT(varchar, @counter), CONVERT(varchar,@date, @counter) 
      SET @counter = @counter + 1
   END TRY
   BEGIN CATCH;
      SET @counter = @counter + 1
      IF @counter >= 150
      BEGIN
         BREAK
      END
   END CATCH
END

SELECT * FROM #dateFormatsAvailable

Output of the above query

Converting Datetime into specific date format without using CONVERT()

In SQL Server 2012 & above, we can use one function"FORMAT"  to format date instead of multiple functions.

Let me explain it with simple examples to convert date into different formats

Declare @Date as Date =Getdate()

Select Format(@Date,'yy.MM.dd') as [yy.MM.dd] 
Select Format(@Date,'yyyy.MM.dd') as [yyyy.MM.dd]
Select Format(@Date,'dd.MM.yyyy') as [dd.MM.yyyy] 
Select Format(@Date,'MM/dd/yy') as [MM/dd/yy] 
Select Format(@Date,'MM/dd/yyyy') as [MM/dd/yyyy] 
Select Format(@Date,'dd/MM/yyyy') as [dd/MM/yyyy]
Select Format(@Date,'dd-MM-yyyy') as [dd-MM-yyyy] 
Select Format(@Date,'dd MMM yyyy') as [dd MMM yyyy]
Select Format(@Date,'MMM dd, yyyy') as [MMM dd, yyyy]  
Select Format(@Date,'MM-dd-yy') as [MM-dd-yy] 
Select Format(@Date,'MM-dd-yyyy') as [MM-dd-yyyy]
Select Format(@Date,'yy/MM/dd') as [yy/MM/dd] 
Select Format(@Date,'yyyy/MM/dd') as [yyyy/MM/dd]
Select Format(@Date,'yyMMdd') as [yyMMdd]
Select Format(@Date,'yyyyMMdd') as [yyyyMMdd] 
Select Format(@Date,'yy-MM-dd') as [yy-MM-dd] 
Select Format(@Date,'yyyy-MM-dd') as [yyyy-MM-dd]

Here is the output of the sample query

You may also like:

Understanding SQL server switch case (With Example)

How to check sql server version? (Various ways explained)

If you have any questions related to this article, ask them in the comments below or create a new question in our questions section.