Skip to main content
C
cfnew
Known Participant
July 25, 2011
Answered

how to get multiple id values

  • July 25, 2011
  • 1 reply
  • 2240 views

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

This topic has been closed for replies.
Correct answer -__cfSearching__-

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


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

1 reply

L
LyndonPatton
Inspiring
July 25, 2011

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>

C
cfnewAuthor
Known Participant
July 26, 2011

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

D
Dan_Bracuk
Inspiring
July 26, 2011

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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices