• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Today's Date within Date Range

Community Beginner ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

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.

TOPICS
Database access

Views

2.2K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

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

it says "now" is an invalid function.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

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 ?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

ok I got it.

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Apr 25, 2012 Apr 25, 2012

Copy link to clipboard

Copied

LATEST

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

Glad you got it sorted.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation