This content has been marked as final. Show 13 replies
> this works fine but i need the first date to be show aswell, so if a user
> chooses 12/12/2006 and 12/01/2007
> it will show item inbetween and including the dates, not sure how to do this
What you are describing *is* the standard behaviour of the BETWEEN
Are you sure you have records that match the first date? What happens if you do this?
where profitdate = #CreateODBCDateTime(Form.StartDate)#
Could be more efficient, but there you are
WHERE ProfitDate = #CreateODBCDateTime(Form.StartDate)#
OR ProfitDate = #CreateODBCDateTime(Form.FinishDate)#
OR ProfitDate Between #CreateODBCDateTime(Form.StartDate)# AND #CreateODBCDateTime(Form.FinishDate)#
ok i see what the problem is now, the form date is in dd/mm/yyyy, so when it gets to my action page and the where clause, it mixes up the mm and the dd, i thought doing a CreateODBCDateTime would sort that problem.
so how would i change this
> so how would i change this
here's an idea
Use the createdate function to translate your form variable into a date.
ok thanks, but not sure how to do it with one text box, which displays format as 22-12-2006
> one text box, which displays format as 22-12-2006
The same ol' code
<cfset variables.startDate = parseDatetime(Form.StartDate)>
TheDate = createdate(
mid(form.startdate, 3, 2),
This assumes you are passing leading zeros. If you are not, treat the from field as a list with hyphen delimiters.
I'm just guessing here, but I suspect parsedatetime will flip you month and day values.
ok dan i have tried BKBK option but it only swopes the days and months if the day is over 12 or it must assume is the month? wierd
anyway my form dosent pass the zeros it os formated like this 1-2-2007
how do i hyphen delimiters?
This is explained in the cfml reference manual for pretty well every list function. If you don't have one, the internet does.
o thanks i have searched the internet but cant find anything, do you know the url?
> i have tried BKBK option but it only swopes the days and months if the
> day is over 12 or it must assume is the month? wierd
Come to think of it, the strange behaviour is to be expected. Coldfusion may assume the date begins with the day or with the month or even with the year. I had assumed it would be consistent for the values from your form. Apparently not.
The sure way to do it is to determine the d-m-y pattern yourself. Parse the input values. Pick out the day, month and year, and create a date. Here's a function that does just that.