SQL Server Convert Datetime to date + Examples

In this SQL Server tutorial, we will discuss How to convert a DateTime to Date in SQL Server, Different SQL Server Conversion Scenarios, and cover the below topic.

• SQL Server Convert Datetime to date in where clause
• SQL Server Convert Datetime to date mm/dd/yyyy
• SQL Server Convert Datetime to date string
• SQL Server Convert Datetime to date without time
• SQL Server Convert Datetime to date and time
• SQL Server Convert Datetime to date hour
• SQL Server Convert Datetime to date column
• SQL Server Convert Datetime to date in query
• SQL Server Convert Datetime to datetimeoffset
• SQL Server Convert Datetime to datetime2
• SQL Server Convert Datetime to DateTime without milliseconds
• SQL Server convert date time to UTC
• SQL Server convert date time to UTC string
• Convert UTC DateTime to PST Datetime in SQL Server

For all the examples, I have used sql server 2019 and sql server management studio.

SQL Server Convert Datetime to date mm/dd/yyyy

In SQL Server to convert a DateTime expression to a specific mm/dd/yyyy format, we can use the Convert() function. In Convert() function we can specify the format that we want as a result by using the style parameter.

SELECT GETDATE() as DateTime, CONVERT(varchar(10),GETDATE(),101) as [mm/dd/yyyy]

• In the above query, we are using the GETDATE() function which returns the current system DateTime value in YYYY-MM-DD hh:mm:ss[.nnn] format.
• After this, we are using the DateTime value to convert into a varchar expression using the Convert() function.
• And in the Convert() function it is important to specify the 101 style code which will convert the DateTime value to mm/dd/yyyy format.

The query will return the following result.

Read: How to create functions in SQL Server

SQL Server Convert Datetime to date string

• In SQL Server, we can easily convert a DateTime expression to a date string using the Convert() function.
• In SQL Server, a string expression is represented as a varchar data type. So by using the Convert() function we have to convert the DateTime format value to varchar.
• And there are various format options available using the Convert() function which is listed below.

Style Code	Format	Query	Result
1	mm/dd/yy	SELECT CONVERT(varchar, GETDATE(), 1)	06/15/21
2	yy.mm.dd	SELECT CONVERT(varchar, GETDATE(), 2)	21.06.15
3	dd/mm/yy	SELECT CONVERT(varchar, GETDATE(), 3)	15/06/21
4	dd.mm.yy	SELECT CONVERT(varchar, GETDATE(), 4)	15.06.21
5	dd-mm-yy	SELECT CONVERT(varchar, GETDATE(), 5)	15-06-21
6	dd-Mon-yy	SELECT CONVERT(varchar, GETDATE(), 6)	15 Jun 21
7	Mon dd, yy	SELECT CONVERT(varchar, GETDATE(), 7)	Jun 15, 21
10	mm-dd-yy	SELECT CONVERT(varchar, GETDATE(), 10)	06-15-21
11	yy/mm/dd	SELECT CONVERT(varchar, GETDATE(), 11)	21/06/15
12	yymmdd	SELECT CONVERT(varchar, GETDATE(), 12)	210615
23	yyyy-mm-dd	SELECT CONVERT(varchar, GETDATE(), 23)	2021-06-15
101	mm/dd/yyyy	SELECT CONVERT(varchar, GETDATE(), 101)	06/15/2021
102	yyyy.mm.dd	SELECT CONVERT(varchar, GETDATE(), 102)	2021.06.15
103	dd/mm/yyyy	SELECT CONVERT(varchar, GETDATE(), 103)	15/06/2021
104	dd.mm.yyyy	SELECT CONVERT(varchar, GETDATE(), 104)	15.06.2021
105	dd-mm-yyyy	SELECT CONVERT(varchar, GETDATE(), 105)	15-06-2021
106	dd Mon yyyy	SELECT CONVERT(varchar, GETDATE(), 106)	15 Jun 2021
107	Mon dd, yyyy	SELECT CONVERT(varchar, GETDATE(), 107)	Jun 15, 2021
110	mm-dd-yyyy	SELECT CONVERT(varchar, GETDATE(), 110)	06-15-2021
111	yyyy/mm/dd	SELECT CONVERT(varchar, GETDATE(), 111)	2021/06/15
112	yyyymmdd	SELECT CONVERT(varchar, GETDATE(), 112)	20210615

In above the queries, we have used the GETDATE() function to get the current system DateTime value, but we can specify any DateTime value instead of the GETDATE() function in the Convert() function.

SQL Server Convert Datetime to date without time

Now to convert a Datetime value to only a date expression without any time, there are 2 functions available in SQL Server that we can use.

Using Cast() to convert Datatime to date without time

SELECT GETDATE() AS [Datetime], CAST( GETDATE() AS Date) AS DateWithoutTime

In the above query first, we are using the GETDATE() function to get the current system DateTime value. And after this we are using the Cast() function to convert the Datetime value to a date expression without any time. And it will return the following output.

Using Convert() to convert Datatime to date without time

SELECT GETDATE() AS [Datetime], 
CONVERT( varchar(10),GETDATE(), 23) AS DateWithoutTime

In the above query, we are using the Convert() function to change the data type of a Datetime expression to varchar. And we are also defining style as 23 which will return the date in yyyy-mm-dd format. We will get the following result out of it.

SQL Server Convert Datetime to date and time

In SQL Server, we can easily convert a standard DateTime format to any other DateTime format using the Convert() function. So by using the Convert() function first we have to convert the DateTime format to varchar and then we can specify the required Datetime format.

And there are various Datetime format options available in the Convert() function which is listed below.

Style Code	Foramt	Query	Result
0	Mon dd yyyy hh:mm AM/PM	SELECT CONVERT(varchar, GETDATE(), 0)	Jun 15 2021 1:04PM
9	Mon dd yyyy hh:mm:ss:nnn AM/PM	SELECT CONVERT(varchar, GETDATE(), 9)	Jun 15 2021 1:07:45:847PM
13	dd Mon yyyy hh:mm:ss:nnn AM/PM	SELECT CONVERT(varchar, GETDATE(), 13)	15 Jun 2021 13:08:11:087
20	yyyy-mm-dd hh:mm:ss	SELECT CONVERT(varchar, GETDATE(), 20)	2021-06-15 13:08:28
21	yyyy-mm-dd hh:mm:ss:nnn	SELECT CONVERT(varchar, GETDATE(), 21)	2021-06-15 13:08:39.960
22	mm/dd/yy hh:mm:ss AM/PM	SELECT CONVERT(varchar, GETDATE(), 22)	06/15/21 1:08:52 PM
25	yyyy-mm-dd hh:mm:ss:nnn	SELECT CONVERT(varchar, GETDATE(), 25)	2021-06-15 13:09:07.027
100	Mon dd yyyy hh:mm AM/PM	SELECT CONVERT(varchar, GETDATE(), 100)	Jun 15 2021 1:09PM
109	Mon dd yyyy hh:mm:ss:nnn AM/PM	SELECT CONVERT(varchar, GETDATE(), 109)	Jun 15 2021 1:09:29:070PM
113	dd Mon yyyy hh:mm:ss:nnn	SELECT CONVERT(varchar, GETDATE(), 113)	15 Jun 2021 13:09:42:497
120	yyyy-mm-dd hh:mm:ss	SELECT CONVERT(varchar, GETDATE(), 120)	2021-06-15 13:09:54
121	yyyy-mm-dd hh:mm:ss:nnn	SELECT CONVERT(varchar, GETDATE(), 121)	2021-06-15 13:10:06.637
126	yyyy-mm-dd T hh:mm:ss:nnn	SELECT CONVERT(varchar, GETDATE(), 126)	2021-06-15T13:10:17.723
127	yyyy-mm-dd T hh:mm:ss:nnn	SELECT CONVERT(varchar, GETDATE(), 127)	2021-06-15T13:10:46.290

Read: SQL Server Convert Function

SQL Server Convert Datetime to date hour

So there are 2 functions available in SQL Server that we can use to convert a Datetime expression to a date hour expression (yyyy-MM-dd:HH). And we will discuss both the function below.

Using Format() to convert Datetime to date hour

The Format() function in SQL Server is used to change the format of numeric or date/time expressions. So we can use the Format() function to convert the Datetime expression to date hour by using the following query.

SELECT GETDATE() AS [Datetime], 
       FORMAT(GETDATE(), 'yyyy-MM-dd:HH') AS [Date-Hour]

In the above query, we are using the GETDATE() function, which returns the current DateTime value. And then we are using the Format() function to change the Datetime format to ‘yyyy-MM-dd:HH’. After successful execution, it will return the following result.

Using Convert() to convert Datetime to date hour

Now we are going to use the Covert() function for converting Datetime value to date hour. But, the Convert() function cannot directly convert DateTime to date hour. For this, we have to add some string manipulation within the query.

SELECT GETDATE() AS [Datetime], 
       CONVERT(varchar(10),GETDATE(),120) +':'+ CONVERT(varchar(2), 
               datepart(hour,GETDATE())) AS [Date-Hour]

In the above query, we are using the Convert() function to first get the date expression out of the Datetime expression and then getting the hours part from the Datetime. In the end, we are concatenating the result. And it will return the following result.

Read: How to execute function in SQL with parameters

SQL Server Convert Datetime to date column

Now to convert a Datetime column to a simple date column, there are 2 functions available in SQL Server.

First is the Cast() function and second is the Convert() function. And both are conversion functions that are used to convert the expression of one data type to another data type. And we will discuss both with the help of an example.

For demonstration consider the following sample table containing random datetime values.

Now we will try to convert the Datetime column to a date column using both Cast() and Convert() functions.

Using Cast() function

SELECT [DateTime], CAST([DateTime] AS date) AS [Date] FROM Sample

In the above query, we are fetching the DateTime column from our table and then we are using the CAST() function to convert the DateTime Column into the date. And it will return the following result.

Using Convert() function

SELECT [DateTime], CONVERT(varchar(10),[DateTime],103) AS [Date] FROM Sample

In the above query, first, we are fetching the DateTime column from the table then we are using the Convert() function to change the Datetime data type to varchar. And we are defining the style parameter as 103 to get the proper date format. After successful execution, it will return the following result.

SQL Server Convert Datetime to datetimeoffset

There are many ways through which we can convert a Datetime expression to datetimeoffset. When converting a DateTime value to datetimeoffset, the result depends on the fractional seconds precision that we give to datetimeoffset, as well as any time zone offset we specify.

The fractional seconds component of the DateTime data type can have up to three digits. Its precision is rounded to the nearest 0.000, 0.003, or 0.007 seconds. On the other side, the datetimeoffset data type allows you to define a fractional seconds precision from 0 to 7.

The datetimeoffset has a time zone offset as well as the ability to keep any offsets from the original value. Datetime, on the other hand, does not have any time zone value.

Now let’s discuss 2 main functions that we can use to convert a Datetime expression to datetimeoffset.

Using Cast() function

SELECT GETDATE() AS 'Datetime',
  CAST(GETDATE() AS datetimeoffset(7)) AS 'datetimeoffset';

In the above query, we get the Datetime value using the GETDATE() function, and then we are using the Cast() function to convert the Datetime value to datetimeoffset. In the end, it will return the following result.

Using Convert() function

SELECT GETDATE() AS 'datetime',
  CONVERT(datetimeoffset(7), GETDATE()) AS 'datetimeoffset';

In the above query, first, we get the Datetime value using the GETDATE() function, and then we are using the Convert() function to convert the Datetime data type to datetimeoffset data type. So after execution, it will return the following result.

SQL Server Convert Datetime to date in query

We can easily convert a Datetime expression to a simple date value by using multiple conversion functions within a query. Let’s understand the implementation with the help of an example.

For example, assume we have the following employee table.

The Joining date field in the above table contains Datetime values. So we are going to use the Convert() function within our query to convert the Datetime value to a date value. And then we are going to use the converted value to filter out results

SELECT * FROM Sample WHERE CONVERT(varchar(10),[JoiningDate],5) > '06-08-2020'

In the above query first, we are using the SELECT statement to get all the columns, and then we are using the WHERE clause. In the WHERE clause, we are using the Convert() function to change the joining date to a specific dd-mm-yy date format. And then we are using a comparison operator to filter out results.

SQL Server Convert Datetime to datetime2

The datetime2 in SQL Server is another data type that is used to define date and time values together. We can consider datetime2 as an extension of DateTime which has a larger date range, a larger default fractional precision, and optional user-specified precision.

The fractional seconds component of the DateTime data type can have up to three digits. Its precision is rounded to the nearest .000, .003, or.007 seconds. On the other hand, the datetime2 data type allows you to define a fractional seconds precision from 0 to 7. And the default fractional seconds precision value for datetime2 is 3.

Converting datetime to datetime2 using Cast()

SELECT GETDATE() AS 'datetime',
  CAST(GETDATE() AS datetime2(7)) AS 'datetime2';

In the above example, we are using the Cast() conversion function within the SELECT query to explicitly convert the DateTime expression to the datetime2 expression. And we have also defined the fractional seconds precision for datetime2 as 7. This means that the fractional second’s value in resulting datetime2 will have up to 7.

In the end, the above query will return the following result.

Converting datetime to datetime2 using Convert()

SELECT GETDATE() AS 'datetime',
  CONVERT(datetime2(7), GETDATE()) AS 'datetime2';

In the above query, we are using the Convert() conversion function within the SELECT query to convert the DateTime expression to the datetime2 expression. And we have also defined the fractional seconds precision for datetime2 as 7. After execution, it will return the following output.

Read: SQL Server Substring Function

SQL Server Convert Datetime to DateTime without milliseconds

Now there are multiple ways through which we can remove the milliseconds part from a Datetime expression. We are discussing a few of them below.

SELECT GETDATE() as 'DateTime', CAST(GETDATE() as datetime2(0)) 
as 'NewDateTime'

In the above query, we are using the Cast() function to change the Datetime expression to datetime2. And for datetime2, we have defined the fractional seconds precision as 0. So, the result from it we will not have any fractional seconds.

SELECT GETDATE() as 'DateTime', CONVERT(datetime2(0),GETDATE(),120) 
as 'NewDateTime'

So in the next query, we are using the Convert() function to convert the Datetime value to datetime2. And again for datetime2, we have defined the fractional seconds precision as 0. So, in the end, the result will not have any fractional seconds.

SELECT GETDATE() as 'DateTime', CONVERT(varchar,GETDATE(),120) as 'NewDateTime'

In the final query, we are using the Convert() function to change the Datetime datatype to varchar. And we have also specified the style parameter as 120. Using which the millisecond will be removed from the result.

All the queries defined in this section are used to remove the milliseconds from the Datetime in SQL Server. All will return the same result

SQL Server Convert Datetime to date in where clause

We can also convert the Datetime data type to a date data type within a WHERE clause in SQL Server. So we can directly convert the Datetime data type to date in the WHERE clause and use it for comparison. Let’s understand this with the help of an example.

Now for demonstration, let’s consider the following sample table having multiple DateTime records.

And now we will try to convert the Datetime column to date within the WHERE clause and make a comparison to extract some records.

SELECT [DateTime] FROM Sample 
       WHERE CONVERT(varchar(10),[DateTime],5) > '06-08-2020'

So in the above query, we are using the SELECT statement to select the DateTime column. After this, we are putting a WHERE clause. And within the WHERE clause, first, we are using the Convert() function to change the Datetime value to a specific dd-mm-yy date format. In the end, we are making a comparison using the converted value.

Hence the query will return the following output.

SQL Server Convert Datetime to UTC

The UTC is our date and time value as it would be in Coordinated Universal Time and to get the current UTC DateTime in SQL Server we can use the GETUTCDATE() function. This function returns the current system DateTime as per Coordinated Universal Time.

So now, let’s understand how we can convert a Datetime value to a UTC DateTime. The SQL Server 2016 or later versions supports multiple UTC zones. And for conversion, we can use any supported UTC zone.

But we cannot directly convert a Datetime value to UTC Datetime. For conversion, we have to use Convert() function with AT TIME ZONE clause.

SELECT GETDATE() as Current_datetime,
       CONVERT(DATETIME, GETDATE() AT TIME ZONE 'Central America Standard Time'
               AT TIME ZONE 'UTC')  AS 'UTC_Datetime';

In the above query, first, we are using the GETDATE() function within the SELECT statement to get the current system Datetime value. After this, we are using the Covert() function to change the Datetime value to UTC Datetime value. And we’re using the AT TIME ZONE clause to define our UTC zone. So after execution, it will return 2 values first is the system Datetime value, and the second is the converted value.

SQL Server Convert Datetime to UTC String

The SQL Server 2016 or later versions supports multiple UTC zones. And we can define our UTC zone by using AT TIME ZONE clause. Now to convert a Datetime expression to a UTC string we can use the following query.

SELECT GETDATE() as Current_datetime,
    CONVERT(VARCHAR(30), GETDATE() AT TIME ZONE 'Central America Standard Time'
            AT TIME ZONE 'UTC')  AS 'UTC_Datetime';

So in the query above, we are using the GETDATE() function to get the current DateTime value. After this, we are using the Convert() function to convert the Datetime data type to varchar. And within the Convert() function we have defined our UTC zone as “Central America Standard Time“. In the end, it will return the following result.

Convert UTC DateTime to PST Datetime in SQL Server

The pst DateTime is used to represent Pacific Standard Time. And as SQL Server 2016 or later supports multiple UTC zone. We can easily convert the local UTC Datetime to PST Datetie by using the query below.

SELECT GETUTCDATE() AS utc_datetime, 
	   CONVERT(DATETIME, GETUTCDATE() AT TIME ZONE 'UTC'
                    AT TIME ZONE 'Pacific Standard Time') AS pst_datetime ;

So in the above query, we are using the GETUTCDATE() function in the SELECT statement to get UTC Datetime. After this, we are using the Convert() function to change UTC Datetime to PST Datetime. And for this, we also have to define the PST Datetime in AT TIME ZONE clause

After execution, it will return thr fllowing result.

In this SQL Server tutorial, we have learned How to convert a DateTime to Date in SQL Server, Different SQL Server Conversion Scenarios, and cover the below topic.

• SQL Server Convert Datetime to date in where clause
• SQL Server Convert Datetime to date mm/dd/yyyy
• SQL Server Convert Datetime to date string
• SQL Server Convert Datetime to date without time
• SQL Server Convert Datetime to date and time
• SQL Server Convert Datetime to date hour
• SQL Server Convert Datetime to date column
• SQL Server Convert Datetime to date in query
• SQL Server Convert Datetime to datetimeoffset
• SQL Server Convert Datetime to datetime2
• SQL Server Convert Datetime to DateTime without milliseconds
• SQL Server convert DateTime to UTC
• SQL Server convert DateTime to UTC string
• Convert UTC DateTime to pst date time in SQL Server