SQL Server Helper - Tip of the Day

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]