The DATEPART date function returns an integer representing the specified DATEPART of the specified date. The syntax of the DATEPART date function is as follows:
DATEPART ( < datepart >, <date> )
The < datepart > is the part of < date > (a date or time value) for which an INTEGER will be returned. The <date> parameter is an expression that can be resolved to a TIME, DATE, SMALLDATETIME, DATETIME, DATETIME2, or DATETIMEOFFSET value. The < date > parameter can be an expression, column expression, user-defined variable, or string literal.
Here are a few uses of the DATEPART date function:
Usage #1 : Get Number of Days in a Month
SELECT DATEPART(DD, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0) - 1) AS [Number of Days in the Month]
Usage #2: Determine if Today is the Last Day of the Month
SELECT CASE WHEN DATEPART(DD, GETDATE()) > DATEPART(DD, GETDATE() + 1)
THEN 'Today is the last day of the month'
ELSE 'Today is NOT the last day of the month' END AS [Last Day]
Usage #3 : Get the Last Day of the Previous Month
SELECT DATEADD(DD, -DATEPART(DD, GETDATE()), GETDATE()) AS [Last Day of Previous Month With Time]
SELECT DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), -DATEPART(DD, GETDATE())) AS [Last Day of Previous Month]
Usage #4 : Return Day of the Week in an Unsupported Language
SELECT CHOOSE( DATEPART(DW, GETDATE()),
'Linggo', 'Lunes', 'Martes', 'Miyerkoles',
'Huwebes', 'Biyernes', 'Sabado' ) AS [Weekday in Tagalog]
Usage #5 : Generate a Random Number
SELECT DATEPART(MCS, SYSDATETIME())
Usage #6 : Age Computation / Calculation
DECLARE @BirthDate DATETIME = '1776/07/04'
SELECT DATEPART(YYYY, GETDATE()) - DATEPART(YYYY, @BirthDate) -
CASE WHEN DATEPART(MM, GETDATE()) > DATEPART(MM, @BirthDate) OR
(DATEPART(MM, GETDATE()) = DATEPART(MM, @BirthDate) AND
DATEPART(DD, GETDATE()) >= DATEPART(DD, @BirthDate))
THEN 0 ELSE 1 END AS [Age of America]
Usage #7 : Format Date in YYYYMMDD Date Format
SELECT DATEPART(YYYY, GETDATE()) * 10000 + DATEPART(MM, GETDATE()) * 100 + DATEPART(DD, GETDATE()) AS [YYYYMMDD]
Usage #8 : Return Name of Month in an Unsupported Language
SELECT CHOOSE( DATEPART(MM, GETDATE()),
'Enero', 'Pebrero', 'Marso', 'Abril', 'Mayo',
'Hunyo', 'Hulyo', 'Agosto', 'Setyembre', 'Oktubre',
'Nobyembre', 'Disyembre' ) AS [Month Name in Tagalog]