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

where between

New Here ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

Hi i have a where clause with a between 2 dates

WHERE ProfitDate Between #CreateODBCDateTime(Form.StartDate)# AND #CreateODBCDateTime(Form.FinishDate)#

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
TOPICS
Advanced techniques

Views

944

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 ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

> 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
operator.

--
Adam

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 ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

Are you sure you have records that match the first date? What happens if you do this?

where profitdate = #CreateODBCDateTime(Form.StartDate)#

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 Expert ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

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)#



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 ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

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

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 Expert ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

> so how would i change this

here's an 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
LEGEND ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

Use the createdate function to translate your form variable into a 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
New Here ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

ok thanks, but not sure how to do it with one text box, which displays format as 22-12-2006

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 Expert ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

> one text box, which displays format as 22-12-2006

The same ol' code

<cfset variables.startDate = parseDatetime(Form.StartDate)>

etc.


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 ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

TheDate = createdate(
right(form.startdate, 4),
mid(form.startdate, 3, 2),
left(form.startdate, 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.

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 ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

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?

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 ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

This is explained in the cfml reference manual for pretty well every list function. If you don't have one, the internet does.

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 ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

o thanks i have searched the internet but cant find anything, do you know the url?

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 Expert ,
Feb 04, 2007 Feb 04, 2007

Copy link to clipboard

Copied

LATEST
> 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.

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