Question

Locked

MS Query - Date Query

By martinbullock15 ·
Hi

I am trying to make this SQL Statment work in MS Excel Query:

dateadd(DAY,-7,getdate())

or a simular query that will work, so that i can pull all information based on todays date -7 days using the date field.

I have tried inserting the above query directly into the SQL option of MS Query but it returns that getdate() is an unregconised statment.

Thanks in advance

Kind Regards

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Try this,

all_terms_idm.standard_date Between #1/1/2007# And #1/31/2007#
OR:
where dt_col between to_date( '01-jan-2002 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )
and to_date( '17-jan-2002 23:59:59', 'dd-mon-yyyy hh24:mi:ss' )


or if you just wanted to supply the days as bind variables (in the form of a string like
'01-jan-2002') you could:

where dt_col between to_date( :bv1, 'dd-mon-yyyy' )
and to_date( :bv2, 'dd-mon-yyyy' ) + (1-1/24/60/60)

that would do the same (adding 1-1/24/60/60 adds 23:59:59


If you are using the mod_plsql module, you should see database sessions that are
connected persistently in "stateful" mode. (query v$session). changing from stateless to
stateful wont necessarily affect performance unless you have lots of requests coming in
simultaneously and actually connecting to the database is the bottleneck (on a smallish
system, this will not be the case). if the PLSQL code or SQL code is what is slow, this
setting won't make a bit of difference! Use tools like dbms_profiler and tkprof to check
this.

Please post back if you have any more problems or questions.

Collapse -

More help here also..

http://www.mrexcel.com/archive/Formulas/23403.html
http://www.mrexcel.com/archive/Data/index.html
http://support.microsoft.com/kb/214094

Please post back if you have any more problems or questions.

Collapse -

Reply

by martinbullock15 In reply to More help here also..

Hi

Thanks for those sites, still didn't get what i wanted.

I'm trying to create the formula so it is constantly updating itself, so it looks for todays days and then returns results within 7 days.

This is for a booking system so i can pull together a report that returns deliveries for the next 7 days, but without editing the query every day to change the dates hence the getdate in my origianl post.

Kind Regards

Collapse -

Daily dose of Excel. link below.

http://www.dailydoseofexcel.com/archives/2004/07/19/networkdays/

Please post back if you have any more problems or questions.

Collapse -

This will also help your with the functions.

http://www.fontstuff.com/VBA/vbatut05.htm
Excel can do this but you will have to work out how that is why i am giving you a few sites so that you can place a few functions in the database to try them out, if you do not have any data to test it out with then i would suggest you make some. No it is not easy if you do not know how to do it but once you know it will become easier. Me i just use the old calender, what comes in and then what goes out, then i back this up. :)

Please post back if you have any more problems or questions.

Collapse -

Thanks

by martinbullock15 In reply to MS Query - Date Query

Thanks for all your help,

I still couldn't get it to work and since it was so much simpler in SQL i used Crystal Reports

Thanks

Collapse -

Sorry to here that. Nice to know that you have found ..

Software that works the way you want it to.
Very rare now a days. Usually it is the software that is in control and you have to get add ons all the time.

Please post back if you have any more problems or questions.

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums