What is the SQL Server equivalent of DATE_TRUNC?

 In SQL Server, there isn't a direct equivalent to the DATE_TRUNC function (as found in databases like PostgreSQL). However, you can achieve the same result by using a combination of the DATEADD and DATEDIFF functions to truncate the date to a specific unit (e.g., year, month, day, etc.).

Examples of truncating dates in SQL Server:

1. Truncate to the beginning of the year:

   SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0);
   

2. Truncate to the beginning of the month:

   SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
   

3. Truncate to the beginning of the day (i.e., set the time part to 00:00:00):

   SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0);
   

4. Truncate to the beginning of the hour:

   SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, GETDATE()), 0);
   

5. Truncate to the beginning of the minute:

   SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()), 0);
   

6. Truncate to the beginning of the second:

   SELECT DATEADD(SECOND, DATEDIFF(SECOND, 0, GETDATE()), 0);
   

Explanation:

• DATEDIFF: This function calculates the number of specified date parts (e.g., years, months, days) between two dates. By using 0 (which corresponds to 1900-01-01) as the starting date, you can count how many units have passed since that date.
• DATEADD: This function adds the calculated number of units back to the base date (0) to give you the truncated value.

By using these combinations, you can truncate dates in SQL Server to various parts of the date, mimicking the behavior of DATE_TRUNC in PostgreSQL or other databases that support it.