January 29, 2009 at 9:54 am
Hi guys!
I have a table with some fiels, including amount, month and year.
What I'm trying to do is a query that reports the following:
select the amount from table where month is previous to the one we're on, including years
Is it possible?
Thanks allot
January 29, 2009 at 10:06 am
use this condition
WHERE MonthColumn = MONTH(DATEADD(m, -1, GETDATE())) AND YearColumn YEAR(DATEADD(m, -1, GETDATE()))
Regards,
Nitin
January 29, 2009 at 10:33 am
Hi nitinpatel31 and thx for the fast reply
I had to change the condition to:
WHERE MonthColumn = MONTH(DATEADD(m, -1, NOW())) AND YearColumn YEAR(DATEADD(m, -1, NOW()))
still the results are only for the one month before NOW() and I need all:
for example:
lets say the oldest record is from 01/01/2008, the query has to return all records from that date until today 29/01/2009 (NOW())
Any ideas?
January 30, 2009 at 2:25 am
Do you store date (as Date/SmallDate type) in single column or have seperate column for month and year?
Regards,
Nitin
January 30, 2009 at 2:27 am
If you have single column for date field you can use below contition
WHERE DateColumn < CAST((CAST(YEAR(GETDATE()) AS VARCHAR) + '/' + CAST(MONTH(GETDATE()) AS VARCHAR) + '/01' ) AS DATE)
This will select all records, except current month.
Regards,
Nitin
January 30, 2009 at 2:37 am
If you store month (monthColumn) and year (yearColumn) in different column then use below condition
WHERE CAST((CAST(YearColumn AS VARCHAR) + '/' + CAST(MonthColumn AS VARCHAR) + '/01') AS DATE)
<= CAST((CAST(YEAR(DATEADD(m, -1, GETDATE())) AS VARCHAR) + '/' + CAST(MONTH(DATEADD(m, -1, GETDATE())) AS VARCHAR) + '/01' ) AS DATE)
Regards,
Nitin
January 30, 2009 at 3:19 am
yes, they are diferent columns for each value. will try the last solution and post a reply in few
thx
January 30, 2009 at 3:27 am
I think CAST() isn't available on ACCESS
Maybe CONVERT()? I'm trying to redo your solution using it
January 30, 2009 at 3:45 am
Yes CAST and GETDATE are sql server function. But you can findout similar function for Access.
Regards,
Nitin
February 1, 2009 at 9:53 am
Pedro,
You can use this in your query to convert each month / year to the 1st day of the month / year:
DateSerial([YearField],[MonthField],1)
That function returns a date, so you could use
WHERE DateSerial([YearField],[MonthField],1) < Now()
however, if that also returns unwanted records from the current month, you might have to use this:
WHERE DateSerial([YearField],[MonthField],1) < DateSerial(Year(Now()),Month(Now()),1)
February 1, 2009 at 10:51 am
Thanks allot William, that was just what I needed
Peace
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply