About DatePart in Date functions

Question

I couldn't understand the difference between

SELECT DATEDIFF(D,'07/09/1978',GETDATE())

SELECT DATEDIFF(DD,'07/09/1978',GETDATE())

Both are giving the same value. Then what is the difference between D, and DD?

I have the same question for

• M, MM
• Q, QQ
• YY, YYYY

Can anybody please explain this to me?

Answer 1

Here is a reference on dateparts and what they mean:

• DATEPART (Transact-SQL) (MSDN)

As you can see, the examples you gave are all identical. However, I know some experts like Aaron Bertrand advocate not using any of the abbreviations, but spelling out the datepart.

See his excellent post on this subject here:

• Bad Habits to Kick : Using shorthand with date/time operations