SQL - DATETRUNC Function

---

---

The SQL DATETRUNC() function is used to truncate a date or time value to a specified datepart.

The function takes two arguments − the first is the datepart to which the date or time value should be truncated, and the second is the date or time value to be truncated.

Syntax

Following is the syntax of the SQL DATETRUNC() function −

DATETRUNC(datepart, date)

Parameters

This function accepts two parameters. The same is described below −

• datepart − The datepart can be one of the following values −

  • year − Truncates the date/time value to the beginning of the specified year.

  • quarter − Truncates the date/time value to the beginning of the specified quarter.

  • month − Truncates the date/time value to the beginning of the specified month.

  • dayofyear − Truncates the date/time value to the beginning of the specified day.

  • day − Truncates the date/time value to the beginning of the specified day.

  • week − Truncates the date/time value to the beginning of the specified week.

  • hour − Truncates the date/time value to the beginning of the specified hour.

  • minute − Truncates the date/time value to the beginning of the specified minute.

  • second − Truncates the date/time value to the beginning of the specified second.

  • millisecond − Truncates the date/time value to the beginning of the specified millisecond.

  • microsecond − Truncates the date/time value to the beginning of the specified microsecond.

• date − This can be any type such as smalldatetime, datetime, date, time, datetime2, datetimeoffset.

Example

Here, we are trying to truncate the datetime value to the beginning of the year using the following query −

SQL> SELECT DATETRUNC(YEAR, '2023-02-20 06:32:15') AS RESULT;

Output

If we execute the above query, the result is produced as follows −

+-----------------------------+
| RESULT                      |
+-----------------------------+
| 2023-01-01 00:00:00.0000000 |
+-----------------------------+

Example

In the following example, we are trying to truncate the datetime value to the beginning of the week (starting on Sunday) −

SQL> SELECT DATETRUNC(WEEK, '2023-02-20 06:32:15') AS RESULT;

Output

When we execute the above query, the output is obtained as follows −

+-----------------------------+
| RESULT                      |
+-----------------------------+
| 2023-02-15 00:00:00.0000000 |
+-----------------------------+

Example

In the following example, we are trying to truncate the datetime value to the beginning of the quarter −

SQL> SELECT DATETRUNC(QUARTER, '2023-05-20 06:32:15') AS RESULT;

Output

The output for the above query is produced as given below −

+-----------------------------+
| RESULT                      |
+-----------------------------+
| 2023-04-01 00:00:00.0000000 |
+-----------------------------+

Example

Here, we are trying to truncate the datetime value to the beginning of the hour using the following query −

SQL> SELECT DATETRUNC(HOUR, '2023-02-20 06:32:15') AS RESULT;

Output

On executing the above query, the output is displayed as follows −

+-----------------------------+
| RESULT                      |
+-----------------------------+
| 2023-02-20 06:00:00.0000000 |
+-----------------------------+

Example

We can truncate the datetime value to the beginning of the second using the following query −

Note − The SYSDATETIME() function retrieves the current date and time.

SQL> SELECT DATETRUNC(SECOND, SYSDATETIME()) AS RESULT;

Output

When we execute the above query, the output is obtained as follows −

+-----------------------------+
| RESULT                      |
+-----------------------------+
| 2023-02-20 16:39:46.0000000 |
+-----------------------------+