Everything Dev Needs to Know About SQL Server Truncate Date

Hey Dev, are you looking for an easy way to remove the time from a date in SQL Server? Look no further than the Truncate Date function. In this article, we’ll cover everything you need to know about using Truncate Date in your SQL Server queries.

What is Truncate Date?

Truncate Date is a SQL Server function that allows you to remove the time portion from a datetime value. It’s a quick and easy way to clean up your data and make sure your queries are accurate.

How to Use Truncate Date

To use Truncate Date, simply call the function and pass in the datetime value you want to truncate. For example:

SQL Query	Result
SELECT TRUNCATE_DATE(‘2022-01-01 12:34:56’)	2022-01-01 00:00:00

This query returns the date ‘2022-01-01’ with the time portion set to 00:00:00.

Truncating Dates by Interval

Truncate Date can also be used to truncate dates by interval – for example, to truncate a datetime value to the beginning of the week, month, or year. Here’s how to do it:

Truncate to Beginning of Week

To truncate a datetime value to the beginning of the week, use the DATEADD and DATEDIFF functions in combination with Truncate Date:

SQL Query	Result
SELECT TRUNCATE_DATE(DATEADD(wk, DATEDIFF(wk, 0, ‘2022-05-20’), 0))	2022-05-16 00:00:00

This query returns the date ‘2022-05-16’ – the beginning of the week that includes May 20, 2022.

Truncate to Beginning of Month

To truncate a datetime value to the beginning of the month, simply call Truncate Date with the ‘month’ datepart:

SQL Query	Result
SELECT TRUNCATE_DATE(‘2022-05-20’, ‘month’)	2022-05-01 00:00:00

This query returns the date ‘2022-05-01’ – the first day of May 2022.

Truncate to Beginning of Year

To truncate a datetime value to the beginning of the year, simply call Truncate Date with the ‘year’ datepart:

SQL Query	Result
SELECT TRUNCATE_DATE(‘2022-05-20’, ‘year’)	2022-01-01 00:00:00

This query returns the date ‘2022-01-01’ – the first day of 2022.

FAQ

Can I truncate datetime values in SQL Server without using Truncate Date?

Yes, you can manually set the time portion of a datetime value to 00:00:00 using the DATEADD function. However, Truncate Date is a simpler and more efficient way to achieve this.

What happens if I pass a non-datetime value to Truncate Date?

If you pass a non-datetime value to Truncate Date, you’ll get an error message. Make sure you’re passing in a valid datetime value!

Can I use Truncate Date with other SQL Server functions?

Yes, you can use Truncate Date in conjunction with other SQL Server functions to manipulate date and time values. For example, you could use it to calculate the number of days between two datetime values, or to group datetime values by week, month, or year.

Is Truncate Date case-sensitive?

No, Truncate Date is not case-sensitive. You can call it with either uppercase or lowercase letters.

Can I truncate datetime values with time zones?

Yes, Truncate Date works with datetime values that include time zones. However, be aware that truncating a datetime value with a time zone will remove the time zone information.

Conclusion

Truncate Date is a powerful and versatile function that can make your SQL Server queries cleaner and more accurate. Whether you’re looking to remove the time portion from a datetime value or truncate dates by interval, Truncate Date is an easy and efficient solution. Happy querying, Dev!