Post reply

Date Math - last day of last month?

  • December 6, 2010 at 2:13 pm

    #388373

    anyone know how to efficiently get the last day of last month. I suspect I can figure out a way to parse it out but am wondering if there is a quick and easy way.

    thanks,

  • December 6, 2010 at 2:17 pm

    #1259217

    select dateadd(mm,datediff(mm,-1,getdate())-1,-1) as LastDayOfLastMonth

  • December 6, 2010 at 2:28 pm

    #1259224

    Thanks - that is a lot easier than the direction i was headed: what about first day of last month?

    I had this:

    CONVERT(VARCHAR, MONTH(DATEADD(mm, -1, GETDATE()))) + '/01/' + CONVERT(VARCHAR, YEAR(DATEADD(mm, -1, GETDATE())))

    pretty convoluted i guess...

  • December 6, 2010 at 2:32 pm

    #1259229

    select dateadd(mm,datediff(mm,0,getdate())-1,0) as FirstDayOfLastMonth

    Lots of resources about SQL Server DATETIME manipulation here:

    Date/Time Info and Script Links

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

  • December 6, 2010 at 2:34 pm

    #1259231

    thanks.

  • December 6, 2010 at 3:05 pm

    #1259249

    And for all the others you may need, check out the common date/time routines link in my signature.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 6, 2010 at 4:32 pm

    #1259296

    Technically, I think the first calc can be reduced ever so slightly 🙂 :

    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1) AS LastDayOfLastMonth

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • December 7, 2010 at 10:38 am

    #1259690

    I think it was a Tuesday.

  • December 8, 2010 at 7:30 am

    #1260093

    Richard Warr (12/7/2010)

    I think it was a Tuesday.

    props to you, sir, that was funny! :-D:hehe:

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply