First Day of Current Month

SELECT DATEADD( mm, DATEDIFF( mm, 0, GETDATE() ), 0 ) AS FirstOfMonth

Since I always manage to get the syntax wrong from memory, above is a SELECT statement that will return the first day of the current month.

Edit: As usual, Joshua has a good point. Also, it just goes to show that re-inventing the wheel every time, instead of re-using code, isn’t the way to go.

This entry was posted in SQL Toolbox and tagged , , . Bookmark the permalink.

One Response to First Day of Current Month

  1. Joshua says:

    “Well I wouldn’t have done it that way.” – Someone to be named later

    Try this instead for a more efficient solution:

    GETDATE() AS CurrentDate
    , DATEDIFF(mm,0,GETDATE()) AS NumberOfMonthsSince
    , DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AS FirstOfMonth
    , DateAdd(dd,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AS EndOfPreviousMonth
    , DateAdd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AS EndOfPreviousMonth_Exact

    The CONVERT(VARCHAR(10) part is good if you want to output just the date without the time as text. But a bit expensive to force two type conversions when the same trick with DATEADD and DATEDIFF can work without the type conversions.

Leave a Reply

Your email address will not be published. Required fields are marked *