Date Problems with SQL

[timwilliams77]

I am trying to run a query where it looks for todays date and then brings back date for the past 30 days

Can any one help?

Thanks

Tim

 

[ca8msm]

You can use the GetDate() function to get todays date, and you can use -30 after it to get the date 30 days in the past. Can you give a bit more detail on the existing table and the required results though?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[timwilliams77]

This is the query, I need to run this automatically
I want it to get todays date then pull back the data for the past 30 days up to todays date

SELECT Applicants.FIRST_NAME as Apllicant_First_Name, Applicants.LAST_NAME as Apllicant_Last_Name, Companies.NAME as Company_Name, CVsSent.DATE_SENT as CV_Date_Sent, Requirements.JOB_TITLE, Interviews.DATE_TIME as Interview_Date, Employees.USER_NAME as Employee_User_Name, Interviews.NUM as Number_Of_Interviews, AppRelInfo.NUM_MARKETED_CVS, Applicants.POST_CODE, AppRelInfo.NUM_CVSENT

FROM (((((Requirements INNER JOIN CVsSent ON Requirements.JOB_ID = CVsSent.JOB_ID) INNER JOIN Companies ON Requirements.COMPANY_ID = Companies.COMPANY_ID) INNER JOIN Interviews ON (CVsSent.CVSENT_ID = Interviews.CVSENT_ID) AND (Requirements.JOB_ID = Interviews.JOB_ID)) INNER JOIN Employees ON Requirements.EMP_ID = Employees.EMP_ID) INNER JOIN Applicants ON CVsSent.APP_ID = Applicants.APP_ID) INNER JOIN AppRelInfo ON CVsSent.APP_ID = AppRelInfo.APP_ID

WHERE (((Interviews.DATE_TIME)>'20070701'))

Order by Employees.user_name asc

 

[ca8msm]

In that case you can just use the GetDate() function I mentioned above and use it instead of a hardcoded date.



____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[r937]

tim, if GetDate() doesn't work, you might be running Access (the nested parentheses are a dead giveaway) so use Date()

r937.com | rudy.ca

 

[timwilliams77]

Thanks ca8msm

Can you show me how you would add the getdate() function and to return data for the past 30 days?

Tim

 

[ca8msm]

What have you tried so far? Post how you've incorporated the GetDate() function and we can help show you where you have gone wrong.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[LNBruno]

Here's one way you could get the two dates for a date range which you should be able to plug in yourself:

select getdate(), getdate()-30

< M!ke >
I am not a hamster and life is not a wheel.

 

[timwilliams77]

SELECT Applicants.FIRST_NAME as Apllicant_First_Name, Applicants.LAST_NAME as Apllicant_Last_Name, Companies.NAME as Company_Name, CVsSent.DATE_SENT as CV_Date_Sent, Requirements.JOB_TITLE, Interviews.DATE_TIME as Interview_Date, Employees.USER_NAME as Employee_User_Name, Interviews.NUM as Number_Of_Interviews, AppRelInfo.NUM_MARKETED_CVS, Applicants.POST_CODE, AppRelInfo.NUM_CVSENT

FROM (((((Requirements INNER JOIN CVsSent ON Requirements.JOB_ID = CVsSent.JOB_ID) INNER JOIN Companies ON Requirements.COMPANY_ID = Companies.COMPANY_ID) INNER JOIN Interviews ON (CVsSent.CVSENT_ID = Interviews.CVSENT_ID) AND (Requirements.JOB_ID = Interviews.JOB_ID)) INNER JOIN Employees ON Requirements.EMP_ID = Employees.EMP_ID) INNER JOIN Applicants ON CVsSent.APP_ID = Applicants.APP_ID) INNER JOIN AppRelInfo ON CVsSent.APP_ID = AppRelInfo.APP_ID

WHERE (((Interviews.DATE_TIME)getdate()-30))

Order by Employees.user_name asc

 

[ca8msm]

A greater than sign (>) would probably help.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[LNBruno]

or would that be > getdate()-31 ?

< M!ke >
I am not a hamster and life is not a wheel.

 

[AlexCuse]

Tim - a most important question was posted above and seemingly not noticed. Is this query for access or SQL Server? I am with Rudy, that it REALLY looks like access, in which case you will need to use the function that he mentioned.

Ignorance of certain subjects is a great part of wisdom