Question

  • Creator
    Topic
  • #2146203

    MS Query – Date Query

    Locked

    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

All Answers

  • Author
    Replies
    • #2459660

      Clarifications

      by martinbullock15 ·

      In reply to MS Query – Date Query

      Clarifications

    • #2459641

      Try this,

      by Anonymous ·

      In reply to MS Query – Date Query

      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.

      • #2459635

        More help here also..

        by Anonymous ·

        In reply to Try this,

        • #2564063

          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

        • #2564058

          Daily dose of Excel. link below.

          by Anonymous ·

          In reply to Reply

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

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

        • #2564056

          This will also help your with the functions.

          by Anonymous ·

          In reply to Daily dose of Excel. link below.

          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.

    • #2564934

      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

      • #2564924

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

        by Anonymous ·

        In reply to Thanks

        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.

Viewing 2 reply threads