DatePart for Two Months Ago

Hello Experts,

Can you give me the formula I would use in the Crystal Reports record selection to return records for two months ago?
I attempted to find it myself searching on 'DatePart', but gave up.

Thank you, Chopp
ChoppAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChoppAuthor Commented:
Or do I need 'dateadd' or 'dateadd' AND 'datepart' together?

I will appreciate your help!
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Hope this is what you meant:

where date_column >= dateadd(mm, -2, date_column)
0
ChoppAuthor Commented:
If I use that record selection, I get ALL the months greater than two months ago, and I only want records from two months ago.

For instance, if I run the report today, I only want records from May.
Any other ideas to get those records?

Thank you, Chopp
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Oops.. a typo as I Just typed in a hurry. And it was

where date_column <= dateadd(mm, -2, date_column)

>> For instance, if I run the report today, I only want records from May.

This made me to analyse a little bit further

-- if You want to get records before 1st of July including all records from May then

where date_column < dateadd(mm,-1, datediff(mm,0, YOURDATE))

-- if You want to get records before 1st of May then

where date_column < dateadd(mm,-2, datediff(mm,0, YOURDATE))

Hope this helps
0
NerdsOfTechTechnology ScientistCommented:
1 month data from your date entered
WHERE date_column <= dateadd(mm,-1, datediff(mm,0, YOURDATE))
AND date_column >= dateadd(mm,-2, datediff(mm,0, YOURDATE))

Open in new window

0
NerdsOfTechTechnology ScientistCommented:
WHERE date_column < dateadd(mm,-1, datediff(mm,0, YOURDATE))
AND date_column >= dateadd(mm,-2, datediff(mm,0, YOURDATE))
0
mlmccCommented:
You have been given a lot of options

If noe of thise work, what exactly do you need if you run the report on 15 Juy 2009?

mlmcc
0
ChoppAuthor Commented:
Hello,

I am still hoping for assistance...

{Cases.DateOpened} <= dateadd("m", -2, currentdate) - This selection returned all months before June.

{Cases.DateOpened} < dateadd("m",-1, datediff("m",0, currentdate)) - This selection gave me the following error message for the "0" "A date is required here".

{Cases.DateOpened} <= dateadd("m",-1, datediff("m",0, currentdate))
AND {Cases.DateOpened} >= dateadd("m",-2, datediff("m",0, currentdate)) - This selection gave the same error message as above.

My goal is to return records for the full month which is 2 months ago from the current month.

Thank you, Chopp
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Have you tried out this one mentioned above:

WHERE date_column < dateadd(mm,-1, datediff(mm,0, YOURDATE))
AND date_column >= dateadd(mm,-2, datediff(mm,0, YOURDATE))

Else try this one out:

where year(date_column) >= year(dateadd(mm, -2, getdate()))
AND month(date_column) <= month(dateadd(mm, -2, getdate()))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChoppAuthor Commented:
Thank you very much for the last example.  I am only using the equals sign in order to get only the records from 2 months ago.  It appears to be working!  Do you know how to resolve the error message that is displayed when using the first example? The error highlights the zero, and states, 'A date is required here'.

Again, thank you very much for this help!
~Chopp

year({IIncident.I_tTimeTransmit}) = year(dateadd("m", -2, currentdate())) and
month({IIncident.I_tTimeTransmit}) = month(dateadd("m", -2, currentdate()))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.