SQL Server DATETRUNC Function

In this SQL Server tutorial, you will learn about the SQL Server DATETRUNC function, where you will be truncating the part of the given date.

After that, you will apply the DATETRUNC() function on the different date string literal and truncate the date to year, month, day, minutes, etc.

Ultimately, you will learn to use the SQL Server DATETRUNC function on the table’s column.

SQL Server DATETRUNC Function

The SQL Server DATETRUNC function is a built-in function that truncates the date to a specific datepart (such as year, month, day, hour, time, seconds, etc) specified by the user.

In other words, if you want to truncate the specific part of the given date, such as year, day, month, etc. Then, pass that date to the DATETRUNC() function with the datepart you want to truncate.

The syntax is given below.

DATETRUNC(part_to_truncate, date)

Where,

• DATETRUNC(): This function truncates the part of the given date.
• part_to_truncate: It is the datepart you want to truncate, such as year, month, day, hour, etc.
• date: The original date used for truncation or from which you truncate the date part. This can be a table column or any expression that can resolve the date and time type. The valid data type is as follows:
  • date, time, datetime, smalldatetime, datetimeoffset, and datetime2.

Below is the list of datepart that you can use within the DATETRUNC() function.

The return type of the SQL Server DATETRUNC function is dynamic, which depends on the input data type. For instance, if you pass the datetime input date, it will return the truncated datepart of type datetime and the same for the other date and time data type.

For example, truncate the year part of the current date and time using the below query.

SELECT DATETRUNC(year, CURRENT_TIMESTAMP) AS TruncatedYear;

The above query truncates the year (2023-01-01) of the current date and time. Let’s understand the query part DATETRUNC(year, CURRENT_TIMESTAMP).

Here, two value is passed to the DATETRUNC() function; first is the year, which is the datepart that you want to truncate, and second is the date, which the CURRENT_TIMESTAMP function returns the current date and time of the system.

But as you can see, the truncated year of the current and date returned by the SQL Server DATETRUNC function is 2023-01-01 00:00:00.000, but here it only shows the year part and keeps all the parts as default values such as month, year, and time components.

But think as it shows only the truncated part and keeps the other date part as the 01; for the time part, it keeps them as zero.

For example, truncating the month part of the date will show the month. It keeps the day as 01 and the time component as 00:00:00.000. Let’s see that using the below query.

SELECT DATETRUNC(month, '2023-12-08 10:30:20.1248567') AS TruncatedMonth;

When you pass the month as the date you want to truncate, it returns the result as 2023-12-01 00:00:00.0000000, as shown in the above output. Here, it included the truncated month part, which is 12, and the year, but all the other units, such as day is 01 and the time component, is zero.

If you pass the day as the datepart that you want to truncate from the date, it will include the year, month, and day of that given date and time but keeps the time component as zero. So run the below query to see that.

SELECT DATETRUNC(day, '2023-12-08 10:30:20.1248567') AS TruncatedDay;

Now, when you pass the day as the datepart to the DATETRUNCT() function, it returns the result as 2023-12-08 00:00:00.0000000. It includes the year, month and the day, but as you can see time component is still zero.

The above three examples show how the DATETRUNC() function truncates the datepart from the given date and returns the truncated part.

Next, let’s truncate the time component of the given datetime value. So first, truncate the hour part of the given date using the query below.

SELECT DATETRUNC(hour, '2023-12-08 10:30:20.1248567') AS TruncatedHour;

It returns the result as 2023-12-08 10:00:00.0000000 containing the hour as 10 when you pass the hour to the DATETRUNCT() function. So this time, it includes the year, month, day, and hour part only, but other parts, such as minutes and seconds, are 0.

So, use the query below to truncate the given date’s minute part.

SELECT DATETRUNC(minute, '2023-12-08 10:30:20.1248567') AS TruncatedMinute;

As you can see from the above query, it truncated the given date ‘2023-12-08 10:30:20.1248567’ to 2023-12-08 10:30:00.0000000, which includes the minute as 30.

Similarly, you can truncate the seconds and milliseconds of the given date and time. Truncating the seconds will include all the parts of the date and time except the milliseconds. Again, truncating the milliseconds will show the complete date and time.

This is how to use the SQL Server DATETRUNC function to truncate the datepart of the specified date.

SQL Server DATETRUNC Function with Table Column

You can also use the column as an expression or date value within the DATETRUNC() function. If you have a column in your table containing the date and time values, you can also truncate the specific part of those values.

For example, you have a Sales table with the columns SaleID, SaleDate, and SaleAmount shown below.

Consider that you have a take where you need to analyse monthly sales trends based on the above table date. You can use the DATETRUNC() function to truncate the dates to the first day of each month, allowing for efficient grouping and analysis.

For that, use the below query.

SELECT DATETRUNC(month, SaleDate) AS MonthStart, SUM(SaleAmount) AS TotalSales
FROM Sales
GROUP BY DATETRUNC(month, SaleDate);

After executing the above query, it shows the sales trends on the first day of each month. For example, the sale for January is 300.50, for Feb is 450.75, and so on for other months.

Now look at the query part DATETRUNC(month, SaleDate). This part truncates the SaleDate column value to the first day of the month in which the sale happened.

For example, if you pass the sale date 2023-12-06 to the DATETRUNC(month, ‘2023-12-06’) function, it would return 2023-01-01.

Then, the query part SUM(SaleAmount) computes the total sales amount for each month. After that, GROUP BY DATETRUNC(month, SaleDate) groups the data by the truncated month, which is the start date, ensuring that the sum of SaleAmount is computed for each month separately.

This is how to use the SQL Server DATETRUNC function on the table column to truncate the date part of those date values in the columns.

Conclusion

In this SQL Server tutorial, you have truncated the given date to a specific date part such as year, month, hour, minute, or day using the SQL Server DATETRUNC function. Also, you applied the DATETRUNC() function in the table to analyse the sale trend, where you have truncated the date to the first month of the SaleDate column.

You may like to read:

• DATEPART Function in SQL Server
• DATENAME Function of SQL Server
• SYSUTCDATETIME Function in SQL Server

Bijay Kumar Sahoo

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.