The inspiration from this post came from when I made a DATETRUNC joke in our Stats 101 lesson and was told that it should have been a DATEPART joke instead. I thought I understood the difference however, shortly found out I did not know as well as I previously thought. This is a good thing, an opportunity to teach myself, and help anyone else who may have the same problem.
I have read several blogs and forum posts online trying to find the best explanation for DATETRUNC vs. DATEPART so I have combined a few. Lets begin.
What is the main difference between Datetrunc vs Datepart?
DATEPART will return a an Integer value (whole number) whereas DATETRUNC will return a Datetime.
EXAMPLE: Trying to output the current month using DATEPART will return 6 because we are in June however, DATETRUNC will return 2023-06-01 00:00:00
Now, you may have noticed that despite only asking for the month using DATETRUNC we have year, day and time. DATETRUNC will go down to the level of detail you request but keep the same format. Let me use a few examples.
DATETRUNC Examples:
DATETRUNC = Continuous (green)
DATETRUNC('year', TODAY()) = 2023-01-01 00:00:00
--> This returns the function at the year level but does not consider the month, day, and time so defaults to 1st January 00:00:00 to keep the same format.
DATETRUNC('month', TODAY()) = 2023-06-01 00:00:00
--> This returns the function at the monthly level but does not consider day and time so defaults to 1st June 00:00:00 to keep the same format.
DATETRUNC('day', TODAY()) = 2023-06-16 00:00:00
--> This returns the function at the daily level but does not consider the time so defaults to 00:00:00 to keep the same format.
Essentially, the more precise date part you ask for, the more detail you receive - as you would expect.
DATEPART Examples:
DATEPART = Discrete (blue)
DATEPART('year', TODAY()) = 2023
--> Returns an Integer value at the yearly level - 2023
DATEPART('month', TODAY()) = 6
--> Returns an Integer value at the yearly level - June = 6
DATEPART('day', TODAY()) = 16
-- Returns an Integer value at the daily level - June 16th = 16
Hopefully this helped you understand the difference between DATETRUNC and DATEPART - thank you for reading!