In this SQL Server tutorial, you will learn about SQL Server Convert Datetime to date, where you will understand the why to convert the datetime value to date.
Then, you will introduced to some function in SQL Server that allows you to convert the given datetime value to date, such as CONVERT(), CAST() and TRY_CONVERT(). Also, you will learn how to convert the datetime value of the table column to date.
SQL Server Convert Datetime to Date
Before converting datetime to date in SQL Server, it is important to understand what datatime and date type in SQL Server.
The datetime datatype stores information about date and time. Date type only keeps information about dates, such as year, month and day.
Why convert datetime to date? You might need to convert datetime to date for several reasons.
- Suppose you need to generate reports, and you may need only the date part of the datetime column.
- In data analysis, you may want to find the trend over days without the time.
- If you have an extensive database, storing only the date part of the datetime can save space.
In SQL Server, there are several that you can use to convert datetime to date; let’s see with examples.
SQL Server Convert Datetime to Date using CONVERT Function
The CONVERT() function transforms the data type of the given value to another data type.
For example, convert the datetime ‘2023-12-04 23:56:33’ to only the date using the below query.
SELECT CONVERT(date, '2023-12-04 23:56:33') AS ConvertToDate;
When passing the data type as date and value as ‘2023-12-04 23:56:33’, the function returns the result as 2023-12-04, which is the date of the given datetime value.
In the query, CONVERT(date, ‘2023-12-04 23:56:33’) transforms the given datetime value ‘2023-12-04 23:56:33’ to date 2023-12-04 only.
Now convert the datetime value of the column in the table to the date value only. For example, you have the table UserActivityLog with columns UserID, Activity, and ActivityTime shown below.
You have a task to find the user activity name and the activity date using the above table. For that, use the below query.
SELECT Activity, CONVERT(Date, ActivityTime) as ActivityDate FROM UserActivityLog;
After executing the above query, it converts the ActivityTime column value to the date value you see in the above output. Four activities are performed on the same date, 2023-11-27.
Here, all the datetime values in the ActivityTime column of the UserActivityLog table are converted to date values.
This is how to convert datetime to date in SQL Server using the CONVERT() function.
SQL Server Convert Datetime to Date using CAST Function
The CAST() function also converts the data type of the given value to a different data type. So here, you will see how to use the CAST() function to convert the datetime value to date.
For example, convert the datetime ‘2022-11-10 15:33:23’ to the date using the query below.
SELECT CAST('2022-11-10 15:33:23' as date) AS ConvertToDate;
When you pass the data type as date and value as ‘2022-11-10 15:33:23’, the CAST() function returns the result as 2022-11-10, which is the date of the given datetime value.
In the query, CAST(‘2022-11-10 15:33:23’ as date) converts the given datetime value ‘2022-11-10 15:33:23’ to date 2022-11-10 only.
Let’s convert the DateTime column in the table to date; for example, you have a table ‘Events’, shown below.
You need only the event name and date, so use the query below.
SELECT EventName, CAST(EventDate as date) as EventDate FROM Events;
As you can see in the above output, the result set contains the event name and its date. For example, the Local Art Fair event was on 2023-11-02.
Understand the query part CAST(EventDate as date). Here, the CAST() function only casts all the datetime values in the EventDate column to date (value) datatype. You can see the event’s name and date in the results set in the two columns, EventName and EventDate.
This is how to convert datetime to date in SQL Server using the CAST() function.
SQL Server Convert Datetime to Date using TRY_CONVERT Function
The TRY_CONVERT() also converts the value’s data type to another data type, but it returns the null where conversion is impossible.
Let’s use the below query and convert the current date and time value to only the date value.
SELECT TRY_CONVERT(date, CURRENT_TIMESTAMP) AS ConvertToDate;
After executing the above query, the TRY_CONVERT() function converts the current timestamp value to date, which is 2023-12-04.
The CURRENT_TIMESTAMP function gets the system’s current date and time. This is how to convert the datetime to date using the TRY_CONVERT() function in SQL Server.
In the same way as CONVERT() and CAST(), you can use the TRY_CONVERT() function on the table column to convert the datetime value in that column into the date value.
If you convert a DateTime column to a date most of the time, use the index converted column for faster query performance.
This is how to use TRY_CONVERT() in SQL Server convert datetime to date.
In this SQL Server tutorial, you learned how to convert the datetime (timestamp) value to a date value using the CONVERT(), CAST() and TRY_CONVERT() functions in SQL Server. Additionally, you applied all these functions on the table column to convert the column value to the date value.
You may like to read:
- DATEPART Function in SQL Server
- SQL Server DATETRUNC Function
- How to use SYSDATETIME Function in SQL Server
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.