[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

get last month

Posted on 2009-02-20
2
Medium Priority
?
356 Views
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)
)'
 
EXEC(@sql1)
 
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
                         ?.dbo.control'
 
EXEC sp_MSForEachDb  @command1= @mapping

Open in new window

0
Comment
Question by:James Murrell
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 23690283
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

0
 
LVL 31

Author Closing Comment

by:James Murrell
ID: 31549152
wow thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

872 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