Home Forums Article Discussions Article Discussions by Author Discuss content posted by Bernabe Diaz t-sql first last day date of the month RE: t-sql first last day date of the month

  • December 1, 2009 at 5:30 am

    #1085706

    A possible alternative is convert/cast with datetime styles (e.g. 112 for ISO in the form of yyyymmdd). This comes in often very handy for date calculations:

    declare @MyDate datetime

    set @MyDate = getdate()

    -- first day of given month (yyyymm01)

    select convert(datetime, convert(varchar, (year(@MyDate) * 10000) + (month(@MyDate) * 100) + 1), 112)

    -- last day of given month (first day of next minus one day -> yyyymm01 + 1 month - 1 day)

    select dateadd(month, 1, convert(datetime, convert(varchar, (year(@MyDate) * 10000) + (month(@MyDate) * 100) + 1), 112)) - 1

    Cheers, R.