Format a date for 1st day of last month.

Posted on 2011-05-06
Last Modified: 2012-05-11
I am trying to capture the 1st day of the previous month,  I need the date in the following format so that I can compare it to another date  2011-01-21 00:00:00.000

The code I have that gives me the date of the 1st day of the previous month is (DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,-1,getdate()))) but this has the time component and i need that set to 000...
Question by:ryanwh
    LVL 32

    Accepted Solution


    select DATEADD(mm, DATEDIFF(mm,0,DATEADD(mm,-1,getdate())), 0)
    LVL 8

    Assisted Solution

    select CAST(FLOOR( CAST( (DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,-1,getdate())))  AS FLOAT ) )AS DATETIME)
    LVL 21

    Expert Comment

    by:Alpesh Patel
    select DateAdd(month,-1,Convert(datetime, convert (varchar,Year(GETDATE())) + '-'+ convert (varchar,Month(GETDATE())) + '-01'))

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    733 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

    23 Experts available now in Live!

    Get 1:1 Help Now