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:

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

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.