Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 Other Topics
 SELECT previous months data
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 09/26/2005 :  07:55:00  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Ben writes "I'm trying to run a select statement in a reporting tool. And one of the select criteria is that it must return all data which was modified last month. So one of the coloumns has this date in the format YYYY/MM.

At the minute this has to be entered manually, as there is a user interface that can be used infront of the SQL.

I was wondering if there was a way to make this run automatically without user entry. i.e. replace:

SELECT *
FROM table1
WHERE date BETWEEN '2005/06' AND '2005/07'

to something more like

SELECT *
FROM table1
WHERE date >= ((sysdate, YYYY MM)MM, -1)
AND date < (sysdate, YYYY MM)

I know the above is a bad attempt but just trying to show the problem.

Thanks in advance."

rrb
SQLTeam Poet Laureate

Australia
1479 Posts

Posted - 10/12/2005 :  19:48:56  Show Profile  Reply with Quote
Hi Ben

So are you saying that your users are entering the SQL string themselves, and you want some way for them not to have to enter the date?

Or do you just want:
select * from table1 where Month(date) = (Month(getdate())-1) and Year(date) = Year(getdate())


--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 10/12/2005 19:49:43
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000