Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

working with dates

Posted on 2008-06-09
8
Medium Priority
?
259 Views
Last Modified: 2011-09-20
I'm trying to run a query all new customers in the last three months and I can't remember how it went for that. I was thinking <(date three months ago) but that didn't work... how do i put it so that it looks up from up to three months ago? Thanks.
0
Comment
Question by:StrategicGnomer
  • 3
  • 3
  • 2
8 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 21746364

select * from TableX
where dateField <=DateAdd("m",-3,Date())
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21746367
what about this:
select ...
  from yourtable
 where registration_date >= dateadd("m", -3, date())

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21746378
that will return all records before March 10,2008.   is that what you want?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:StrategicGnomer
ID: 21746386
here is the query, it didn't work...

SELECT tblCustomer.ftxNameFirst, tblCustomer.ftxNameLast, TblServProv.SenttoREP
FROM (tblCustomer LEFT JOIN tblElectricData ON tblCustomer.fidCustomer = tblElectricData.fidCustomer) LEFT JOIN TblServProv ON tblElectricData.fidElectric = TblServProv.ID
WHERE (TblServProv.SenttoREP)<=DateAdd("m",-3,Date());
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1200 total points
ID: 21746403
you have to put the condition on left joined table into the join itself:
SELECT tblCustomer.ftxNameFirst, tblCustomer.ftxNameLast, TblServProv.SenttoREP
FROM (tblCustomer LEFT JOIN tblElectricData ON tblCustomer.fidCustomer = tblElectricData.fidCustomer) 
LEFT JOIN TblServProv ON ( tblElectricData.fidElectric = TblServProv.ID AND TblServProv.SenttoREP >= DateAdd("m",-3,Date()));

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21746405
try angeIII code,  use >=  instead of <=
0
 

Author Comment

by:StrategicGnomer
ID: 21746831
did that, it says syntax error in join... keep in mind i'm using access 2003 so i think it behaves a little differently then say SQL Server or MySQL but i'm not too sure.
0
 

Author Comment

by:StrategicGnomer
ID: 21746851
oh nm, just a small error, i got it. Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

876 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