ms sql + dates

Posted on 2011-05-05
Last Modified: 2012-06-27
i would like to get the first day and last of the month of the previous month, i am using the following code, but would like the time for the startdate be 00:00 and end date 23:59 how can i modify to accomplish.

declare @StartDate datetime,
            @EndDate datetime

select @StartDate = DATEADD(dd,-(DAY(DATEADD(mm,1,GetDate()))-1),DATEADD(mm,-1,GetDate()))
select @EndDate = DATEADD(dd, -DAY(DATEADD(m,1,GetDate())), DATEADD(m,0,GetDate()))
Question by:dkilby
    LVL 51

    Accepted Solution

    Here's one way. There may be a more elegant approach for 2008

    declare @StartDate datetime, @EndDate datetime
    set @StartDate = dateAdd(m, -1, convert(datetime, convert(varchar(6), getDate(), 112) +'01', 112))
    set @endDate = dateAdd(n, -1, dateAdd(m, 1, @startDate))
    select @startDate, @endDate

    Open in new window


    Author Closing Comment

    thanks for the help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now