get last month

Posted on 2009-02-20
Last Modified: 2012-05-06
with this query below, need to get data only from last month

where lookup_date between (last month) any ideas
declare @sql1 varchar(1000) -- table structure

DECLARE @tableName1  varchar (8000) -- table name as previous month

declare @mapping varchar(8000) --sql query for each db

SET @tableName1 = '[dbo].[mapping' + DATENAME(m, DATEADD(month, -1, GETDATE()))+ CAST(YEAR(GETDATE()) AS char(4))+']' -- gets previous month

declare @lmon =DATENAME(m, DATEADD(month, -1, GETDATE()))+ CAST(YEAR(GETDATE()) AS char(4))

-- create table

SET @sql1 = 'CREATE TABLE ' + @tableName1 +'


control_id varchar(200),

lookup_date dattime  ,

pca_external_lookups varchar(200),

member_id varchar(200)



set @mapping = ' insert into '+ @tableName1 + ' select ?.dbo.control.member_id, ?.dbo.map_lookups.lookup_date, ?.dbo.map_lookups.pca_external_lookups, ?.dbo.control.member_id

FROM            ?.dbo.map_lookups CROSS JOIN


EXEC sp_MSForEachDb  @command1= @mapping

Open in new window

Question by:James Murrell
    LVL 142

    Accepted Solution

    this should do:
    where lookup_date >= dateadd(month, -1, convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120))
      and lookup_date < convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120)

    Open in new window

    LVL 31

    Author Closing Comment

    by:James Murrell
    wow thanks

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    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…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    734 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

    19 Experts available now in Live!

    Get 1:1 Help Now