Skip navigation
TheScarecrow
TheScarecrow
Currently Being Moderated

Today's Date within Date Range

Apr 25, 2012 8:48 AM

I have a BeginDate and EndDate in a database.  I want to be able to list those records if todays date is within the date range of BeginDate and EndDate.  I am unsure of the coding on this.  Has anyone done this and can give me some help? Thanks.

992 Views   11 Replies   Latest reply: Owain North, Apr 25, 2012 11:34 PM
 
Replies
  • Owain North
    Currently Being Moderated
    1. Owain North,
    Apr 25, 2012 8:50 AM   in reply to TheScarecrow
    Report

    Depends on the database platform as to exactly what function you'd use, but essentially:

     

    SELECT cols

    FROM table

    WHERE now() > BeginDate

    AND now < EndDate

     

    That's all there is to it.

     

    Some databases let you do BETWEEN clauses, so even neater

     

    WHERE now() BETWEEN StartDate and EndDate.

     

    Just be careful to do some testing as to what happens when today *is* the end or start date, you may want them included or excluded depending on your requirements.

     
    |
    Mark as:
  • TheScarecrow
    Currently Being Moderated
    2. TheScarecrow,
    Apr 25, 2012 9:08 AM   in reply to Owain North
    Report

    Ok that makes sence. I am getting an error with  WHERE now() BETWEEN begindate and end_date

    it says "now" is an invalid function.

     
    |
    Mark as:
  • Owain North
    Currently Being Moderated
    3. Owain North,
    Apr 25, 2012 9:08 AM   in reply to TheScarecrow
    Report

    Right, so you need to find out what the correct function name is for your database platform.

     
    |
    Mark as:
  • TheScarecrow
    Currently Being Moderated
    4. TheScarecrow,
    Apr 25, 2012 9:15 AM   in reply to Owain North
    Report

    I am using Filemaker but I thought Now() was a coldfusion call to the server to get the current date from the server.

     
    |
    Mark as:
  • Owain North
    Currently Being Moderated
    5. Owain North,
    Apr 25, 2012 9:17 AM   in reply to TheScarecrow
    Report

    You're not using the CF function, as to do so would mean wrapping up the CF now() call in a CFQueryParam and passing it around as a variable, which is inefficient. I've never used FileMaker but Googled "Filemaker get current date" and it says to use "Get(CurrentDate)" - give that a go.

     
    |
    Mark as:
  • TheScarecrow
    Currently Being Moderated
    6. TheScarecrow,
    Apr 25, 2012 9:57 AM   in reply to Owain North
    Report

    That is weird.  I just get another error. So should it be like this WHERE #Get(CurrentDate)# BETWEEN begindate and end_date or do I have to convert it using CREATEODBCDATETIME ?

     
    |
    Mark as:
  • Owain North
    Currently Being Moderated
    7. Owain North,
    Apr 25, 2012 9:59 AM   in reply to TheScarecrow
    Report

    No, you shouldn't have to convert anything.

     

    Assuming "Get(CurrentDate)" is correct (which as I said was from a quick Google, I've never used the DB you're using) it will return a date object. Assuming your dates are stored in the database as dates as well and not strings or something silly, it should be fine.

     

    What does the error say?

     
    |
    Mark as:
  • TheScarecrow
    Currently Being Moderated
    8. TheScarecrow,
    Apr 25, 2012 10:01 AM   in reply to Owain North
    Report
    Error Occurred While Processing Request

    Variable GET is undefined

     

    Ok That is what I thought.  I have tried it without the # and I get There is an error in the syntax of the query.

     
    |
    Mark as:
  • Owain North
    Currently Being Moderated
    9. Owain North,
    Apr 25, 2012 10:07 AM   in reply to TheScarecrow
    Report

    Okay, well you need to help me out and find out what the function name is you need, as there's little point me doing that for you. You'll need to check the documentation for the database platform.

     
    |
    Mark as:
  • TheScarecrow
    Currently Being Moderated
    10. TheScarecrow,
    Apr 25, 2012 11:08 AM   in reply to TheScarecrow
    Report

    ok I got it.

    #CreateODBCDate(Now())# BETWEEN begindate and end_date

     
    |
    Mark as:
  • Owain North
    Currently Being Moderated
    11. Owain North,
    Apr 25, 2012 11:34 PM   in reply to TheScarecrow
    Report

    Okay that's not quite what I meant but hey, if it works it works!

     

    Glad you got it sorted.

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points