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

how to get multiple id values

New Here ,
Jul 25, 2011 Jul 25, 2011

Copy link to clipboard

Copied

Hi ,

I had  database table like this

ID    start_date     end_date

1      9/26/2009        10/2/2009
2     10/16/2010       10/22/2010
3     10/23/2010       10/29/2010
4     10/30/2010       11/5/2010

here is the scenario.When user selects start_date as 9/26/2009 and end_date as 11/5/2010,then i need to get ID values (1,2,3,4).

i tried in diff ways and not able to acheive that,can some one help me in this.

Thanks

TOPICS
Getting started

Views

1.7K

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

correct answers 1 Correct answer

Valorous Hero , Jul 26, 2011 Jul 26, 2011

Yes, it can be an elegant solution to the age old "date" problem 😉 Though you should not need it on the startDate side. Your createODBCDate should take care of things.

ie

WHERE start_date >= #CreateODBCDate(form.startDate)#

AND       end_Date < #DateAdd("d",1,CreateODBCDate(form.endDate))#

WHERE start_Date >= 09/26/2009 12:00:00 AM

AND       end_Date <    11/6/2010 12:00:00 AM

Votes

Translate

Translate
Participant ,
Jul 25, 2011 Jul 25, 2011

Copy link to clipboard

Copied

Something like this?

<cfquery name="myQuery" datasource="myDataSource">

  Select id

  From myTable

  Where start_date>=#CreateODBCDate("Form.StartDate")#

  And End_Date<=#CreateODBCDate("Form.EndDate")#

</cfquery>

<cfset idList=ValueList(myQuery.ID)>

<cfoutput>

  #myList#

</cfoutput>

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
New Here ,
Jul 26, 2011 Jul 26, 2011

Copy link to clipboard

Copied

HI Lyndon,

Thanks for your reply but i have a problem with the data from table .

In database table i had a time stamp in end_date column and data is like this

ID    start_date         end_date

1      9/26/2009        10/2/2009 11:59:59 PM
2     10/16/2010       10/22/2010 11:59:59 PM
3     10/23/2010       10/29/2010 11:59:59 PM
4     10/30/2010       11/5/2010  11:59:59 PM

when i tried this query

<cfquery name="test" datasource="itoetools">
Select ID from TEST
  Where start_date>=to_date('9/26/2009','mm/dd/yyyy')
  And End_Date <= trunc(to_date('11/5/2010','mm/dd/yyyy'))
</cfquery>

My result is coming only ID Values(1,2,3) and ID value 4 is not coming.

Do i need to do nything else to get that.

My database is ORacle.

Thanks

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
LEGEND ,
Jul 26, 2011 Jul 26, 2011

Copy link to clipboard

Copied

Regarding:

Do i need to do nything else to get that.

Reading the answers to the other thread you started on this topic is a good place to start.

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
Participant ,
Jul 26, 2011 Jul 26, 2011

Copy link to clipboard

Copied

I have no idea what the functions you are using do, as opposed the my suggested "CreateODBCDate".

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
New Here ,
Jul 26, 2011 Jul 26, 2011

Copy link to clipboard

Copied

i used the same code of you

ID    start_date         end_date

1      9/26/2009        10/2/2009 11:59:59 PM
2     10/16/2010       10/22/2010 11:59:59 PM
3     10/23/2010       10/29/2010 11:59:59 PM
4     10/30/2010       11/5/2010  11:59:59 PM

<cfquery name="test2" datasource="testing">
Select ID from TEST
  Where start_date>=#CreateODBCDate("9/26/2009")#
  And End_Date <= #CreateODBCDate("11/5/2010")#
</cfquery>

but still My result is coming only ID Values(1,2,3) and ID value 4 is not coming.

any idea

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
Valorous Hero ,
Jul 26, 2011 Jul 26, 2011

Copy link to clipboard

Copied

Because your expression evaluates to End_Date <= 11/5/2010 12:00:00 AM.

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
New Here ,
Jul 26, 2011 Jul 26, 2011

Copy link to clipboard

Copied

thanks for reply cfSearching ,

how can i deal with that to get that id value also

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
Valorous Hero ,
Jul 26, 2011 Jul 26, 2011

Copy link to clipboard

Copied

Change your expression so it includes all times on that date.  A common technique is using the day after the one you want. By using less than you implicitly include all times during the date 11/05/2011.

         AND End_Date < 11/6/2010 12:00:00 AM

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
Participant ,
Jul 26, 2011 Jul 26, 2011

Copy link to clipboard

Copied

Good catch, as always, CFsearching. I've had this problem before and didn't think of it...

cfnew, if you're getting start and end from a form this is how I do it:

<cfquery name="test2" datasource="testing">
Select ID from TEST
  Where start_date>#DateAdd("d",(-1),CreateODBCDate(Form.StartDate))#

  And End_Date <#DateAdd("d",1,CreateODBCDate(Form.StartDate))#

</cfquery>

This works if you want to ignore the time part of the dates

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
Valorous Hero ,
Jul 26, 2011 Jul 26, 2011

Copy link to clipboard

Copied

Yes, it can be an elegant solution to the age old "date" problem 😉 Though you should not need it on the startDate side. Your createODBCDate should take care of things.

ie

WHERE start_date >= #CreateODBCDate(form.startDate)#

AND       end_Date < #DateAdd("d",1,CreateODBCDate(form.endDate))#

WHERE start_Date >= 09/26/2009 12:00:00 AM

AND       end_Date <    11/6/2010 12:00:00 AM

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
New Here ,
Jul 28, 2011 Jul 28, 2011

Copy link to clipboard

Copied

Hi Thanks for your nice answer it worked nice , now i need small help again in other scenario like this.

Below are the dates and i just

ID    start_date         end_date

1      9/26/2009        10/2/2009 11:59:59 PM
2     10/16/2010       10/22/2010 11:59:59 PM
3     10/23/2010       10/29/2010 11:59:59 PM
4     10/30/2010       11/5/2010  11:59:59 PM

i just need to write a sql query when user enteredoen date  for example 9/28/2009 i need to get ID value 1.

Is there anyway we implement this.

Thanks

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
Valorous Hero ,
Jul 28, 2011 Jul 28, 2011

Copy link to clipboard

Copied

i just need to write a sql query when user enteredoen

date  for example 9/28/2009 i need to get ID value 1.

Like I mentioned on your other thread, knowing your application's logic is important. What is the reason ID should be selected when 9/28/2009 is entered? I can guess. But there is a good chance my guess will be wrong

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
LEGEND ,
Jul 28, 2011 Jul 28, 2011

Copy link to clipboard

Copied

You do it the same way as before, but with a different end date.  Read the entire thread again and the value of that end date should be apparent.

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
New Here ,
Jul 28, 2011 Jul 28, 2011

Copy link to clipboard

Copied

user is ot going to enter any enddate as user is allowed to enter one date and based on the date user entered i need to get the id value.

this is for the reporting purpose and so i need to get single ID value.

example 9/28/2009 date lies between 9/26/2009 to 09/30/2009 so i need that ID value for this date range.

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
Valorous Hero ,
Jul 28, 2011 Jul 28, 2011

Copy link to clipboard

Copied

example 9/28/2009 date lies between 9/26/2009 to 09/30/2009 so i need that ID value for this date range.

Good.  Substitute the variable and column names and that is almost word for word how you might write it in sql:

        WHERE    #YourDateVariable#  BETWEEN TheStartDateCol AND TheEndDateCol

... in other words

         #YourDateVariable#  >= TheStartDateCol AND #YourDateVariable# <= TheEndDateCol

But because of the time portion in your fields, that is not quite right.  Just reverse the > and <'s in the original sql and you should be all set.

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
LEGEND ,
Jul 28, 2011 Jul 28, 2011

Copy link to clipboard

Copied

LATEST

That's correct, the End Date will not come from the user.  You have to base it on the Start 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
Valorous Hero ,
Jul 28, 2011 Jul 28, 2011

Copy link to clipboard

Copied

The correct answer really depends on why ID 1 matches. Without that information this is all just guesswork.

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