Tip of the Day : Similarities and Differences Between MONEY and SMALLMONEY Data Types

SQL Server Helper - Tip of the Day

Example Uses of the DATEPART Date Function

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]

Back to Tip of the Day List Next Tip