How to Remove Times from Dates in SQL Server

In SQL Server, there are Date and DateTime data types to store Date and Time values. There can be situations when only the date value needs to be displayed or to do date calculations for date add, date difference functions, and between two dates from datetime values. So, to remove the Time part from dates with data and time, many methods can be used based on need and usage.

In this article, we will discuss methods such as CAST to Date Data Type, CONVERT Function with Style Code, and Format Function (For SQL Version 2012 and Later) to Remove Times from Dates in SQL Server in detail.

Introduction to Remove Times from Dates in SQL Server

There are many approaches to remove Time from Date Columns with data and time. Below are the common approaches used now. This can vary for older SQL Versions.

1. CAST To Date Data Type
2. CONVERT Function with Style Code
3. Format Function

Below is the sample data from EmployeesInfo with the ‘DateJoined‘ date time column. This data is used in the examples below.

EmployeesInfo Table data

Method 1: CAST to Date Data Type

The CAST method directly converts the datetime value to date data type which removes the time component.

Syntax:

CAST(DateTimeValue AS Date) 

Explanation: For demonstrating the CAST function using a Select Query, we will use a table with a datetime column and sample data as below:

Example 1: Fetch Employee Details and DateJoined Column

Let’s Retrieve and display the employee ID, employee name, and cast the DateJoined column as a Date type in the ‘Date_Of_Join‘ column from the table EmployeesInfo in a MariaDB database.

Query:

Select EmployeeID,EmployeeName,CAST(DateJoined AS Date) as Date_Of_Join from EmployeesInfo

Output:

CAST example

Explanation: In the above Query we have fetched the mentioned data.

Method 2: CONVERT Function with Style Code

The CONVERT function is used to convert the datetime value into date only.

Syntax:

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

Explanation:

The CONVERT function has three parts as explained below:

• Datatype and length: The datatype can be generally be varchar and length can be generally 10. But for displaying Month part in words like the below the length may vary.
• Date Expression: The Date Expression is a date value or a Date column name from a Table.
• Date Value Style: The Style is the format of the Date display. The style or date format can be of many different types. For example, below are some of the formats available for CONVERT function.

Query:

Select  CONVERT (varchar (10), getdate(), 101) As DateOnly
Select  CONVERT (varchar (10), getdate(), 102) As DateOnly
Select  CONVERT (varchar (10), getdate(), 103) As DateOnly
Select  CONVERT (varchar (10), getdate(), 104) As DateOnly
Select  CONVERT (varchar (10), getdate(), 105) As DateOnly
Select  CONVERT (varchar (10), getdate(), 110) As DateOnly
Select  CONVERT (varchar (10), getdate(), 111) As DateOnly

Explanation: Below is an example for how CONVERT works with a table with datetime column using EmployeesInfo table.

Example 1: Fetch Employees Details and Convert DateJoined Column to a Formatted String

Let’s Retrieve and display the employee ID, employee name, and convert the DateJoined column to a formatted string in the ‘Date_Of_Join‘ column using the ‘111‘ style (yyyy/mm/dd) from the table EmployeesInfo in a MariaDB database.

Query:

Select EmployeeID,EmployeeName,CONVERT (varchar (10), DateJoined, 111) as Date_Of_Join from EmployeesInfo

Output:

CONVERT example

Explanation: In the above Query, we have fetched the mentioned data.

Method 3: Format Function (For SQL Version 2012 and Later)

The FORMAT function can also be used to get only the date part from a datetime expression or date column in a table.

Syntax:

FORMAT(datevalue, format [, culture])

Explanation: In Format function, datevalue is the data expression or date column and format is required date format. Culture is an optional option which is country specific date format.

Example 1: Fetch Employees Details and Formatted Date of Joining

Let’s Retrieve and display the employee ID, employee name, and formatted date of joining from the table EmployeesInfo in a MariaDB database. The formatted date should be presented in the ‘dd/MM/yyyy’ format.

Query:

Select EmployeeID,EmployeeName,FORMAT( DateJoined, 'dd/MM/yyyy') as Date_Of_Join from EmployeesInfo

Output:

FORMAT example

Explanation: We have successfully fetched all information with this format ‘dd/MM/yyyy’.

Conclusion

In this article, We have discussed about how to remove Time from Date string or expression or date value in a column in Table. We discussed about three function methods namely CAST, CONVERT and FORMAT. The FORMAT function is available for SQL Version 2012 and above. Also, it is to be noted that the there is Date data type which stores DATE value only in SQL Version from 2008 and above.