Current Month Code 2

[marie515]

Hi all,

I pulled the following code from Microsoft support to pull records based on my "closeDate" field.

I want to pull all records closed in the current month. I used the following criteria:

DateSerial(Year(Date()),Month(Date()),1)

I'm not getting all of the records closed in March however, any ideas what I might be doing wrong?

Thanks.

 

[cheerio]

The expression DateSerial(Year(Date()),Month(Date()),1)
today simply evaluates as March 1 2004 so that is the only day you are selecting.

 

[marie515]

so, if I use the following, will that work?

Between DateSerial(Year(Date()),Month(Date()),1)
and DateSerial(Year(Date()),Month(Date()),31)

Thanks.

 

[cheerio]

You are on the right lines but I would suggest for the second expression that you try

DateSerial(Year(Date()),1+Month(Date()),0)

The 0th day of next month evaluates as the last day of this month.

The problem with your solution is that next month the 31st day of April will evaluate as the 1st of May which you won't want.

 

[TonyJollans]

Hi marie515,

I wouldn't complicate it with the day at all. Just compare the year and month, either something like this using two comparisons ..

[blue][tt]Year([Closedate]) = Year(Date) AND Month([Closedate]) = Month(Date)[/tt][/blue]

.. or like this using a single comparison ..

[blue][tt]Format([Closedate],"yyyymm") = Format(Date,"yyyymm")[/tt][/blue]


Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[marie515]

thanks guys! This worked

 

[r937]

one thing to remember, tony, is that if you apply a function to a table column, you will likely cause the database engine to disregard any index on that column, and do a table scan instead

thus, do not code

... where xxx(datecol) = somevalue

but rather

... where datecol = yyyy(somevalue)

a preferred approach for all rows in the current month is

where datecol >= 
        dateadd("d",-day(date())+1,date())
  and datecol <  
        dateadd("m",1,
        dateadd("d",-day(date())+1,date())
               )

here dateadd("d",-day(date())+1,date()) is the first day of the current month, and you just add one month to that to get the first day of next month

the database engine can then pre-evaluate the two dates based on date(), and use these two values as the extrema for an index search



rudy

http://r937.com/

SQL Consulting

 

[TonyJollans]

Hi Rudy,

An excellent point well worth highlighting.

A star for you.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[AndyHorn]

I've found Rudy's code to be really useful for me and was wondering if/how it's easy to change his code to reflect the current year instead of current month?

I had a go, but I didn't get the results I wanted. I don't really understand the code. This is what I tried;

WHERE (((PURCHASE_TRANSACTIONS.TRANSACTION_DATE)>=DateAdd("m",-Month(Date())+1,Date()) And (PURCHASE_TRANSACTIONS.TRANSACTION_DATE)<DateAdd("y",1,DateAdd("m",-Month(Date())+1,Date()))))

Thanks.
Andy

 

[r937]

for current year, assuming transactions only go up to today,

WHERE TRANSACTION_DATE
between CVDate( Year(Date()) & '-01-01' )
and Date()

rudy

http://r937.com/

SQL Consulting

 

[AndyHorn]

Perfect. Thanks.