Software

Ask something
Question Locked

MS Query - Date Query

0 helpful
8 replies

by martinbullock15

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

8 replies

by Former member

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.

by martinbullock15

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

by Former member

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.

by martinbullock15

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

by Former member

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.

This discussion is closed to new replies.