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
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
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>
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
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.
Copy link to clipboard
Copied
I have no idea what the functions you are using do, as opposed the my suggested "CreateODBCDate".
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
Copy link to clipboard
Copied
Because your expression evaluates to End_Date <= 11/5/2010 12:00:00 AM.
Copy link to clipboard
Copied
thanks for reply cfSearching ,
how can i deal with that to get that id value also
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
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
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
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
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
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.
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.
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.
Copy link to clipboard
Copied
That's correct, the End Date will not come from the user. You have to base it on the Start Date.
Copy link to clipboard
Copied
The correct answer really depends on why ID 1 matches. Without that information this is all just guesswork.