SQL Between Statement

[Dan100]

I have a table that has two fields called 'DateEnding' and 'Period'. There
could be up to 3 different periods for each DateEnding. The problem I'm having
is selecting a range using the BETWEEN. It's easy to say, 'DateEnding BETWEEN
FromDate AND ToDate'. However I may want to select from DateEnding, Period 3
to DateEnding Period 2. Is this possible or do I have to create a field in the
Database that will represent the DatePeriodEnding and use the BETWEEN on that
field. I know that's one way but that also means I have to maintain the extra
field.

Thanks in advance for your help.
Dan

[JMGibson3]

The easiest way to handle this is to concatenate the two fields into a single
text field, and run a WHERE clause on the concatenation. Syntax varies widely
amongst the various DB's. You will also have to get into DB functions to
convert non-text values to text. In Access it would be something like the
following assuming Period is one character text. If number you'd need a CSTR
function also, or maybe another FORMAT to tack on leading zeros for a fixed
length text result to be compared):

<cfset varFrom = DateFormat(Form.FromDate,"YYMMDD") & Form.FromPeriod>
<cfset varThru = DateFormat(Form.ThruDate,"YYMMDD") & Form.ThruPeriod>

WHERE FORMAT(DateEnding,"YYMMDD")&Period BETWEEN '#varFrom#' AND '#varThru#'

[Dan100]

JMGibson,

I'm using SQL 2000 Server and it does not like the & before the Period. Is there another way to join the two fields?

Thanks
Dan

[The ScareCrow]

In sql server you use the "+" to concat.

can I ask for some sample values of what's in the "period" column and what is
being passed from the form to the query.
Then I may be able to suggest a solution

Ken

[Dan100]

Ken,

Period is an 'int' and can be 1, 2 or 3. DateEnding is a 'datetime'

Just prior to the Query I'm building the varFrom and varTo as follows
<cfset varFrom=CreateODBCDateTime(form.FromDateEnding)&st r(form.ToPeriod)>
<cfset varTo=CreateODBCDateTime(form.ToDateEnding)&str(fo rm.ToPeriod)>
form.FromDateEnding and form.ToDateEnding are both selected from a dropdown of
dates pulled from the database.

Here's the current Where:
where ((DateEnding+convert(varchar(8),period)) between '#varFrom#' AND
'#varTo#')

Thanks
Dan

[The ScareCrow]

If I understand this correctly, then you don't need to concat the values

The following should work

WHERE DateEnding BETWEEN #CreateODBCDate(Form.FromDate)# AND
#CreateODBCDate(Form.ThruDate)#
AND Period = #Form.ThruPeriod#

Note:
If the DateEnding has time values as well, that is the values where inserted
using CreateODBCDateTime or getdate()
Then you will need to add a day to the thrudate. This is because when a date
is passed to createodbcdate it automatically sets the time to 00:00:00
(midnight)

Ken

[Dan100]

Ken,

That was a typo...it should have been form.FromPeriod since Period can be 1, 2
or 3.
<cfset varFrom=CreateODBCDateTime(form.FromDateEnding)&st r(form.FromPeriod)>
<cfset varTo=CreateODBCDateTime(form.ToDateEnding)&str(fo rm.ToPeriod)>

I do need to concat the values but a Date type and string type don't seem to
mix.

Thanks
Dan

[Dan100]

Sample Data
DateEnding Period
05/30/2005 1
05/30/2005 2
05/30/2005 3
06/14/2005 1
06/14/2005 2
06/14/2005 3
06/30/2005 1
06/30/2005 2
06/30/2005 3
etc..

[The ScareCrow]

Okay, I'm not really sure why you need to concat (I would need to see the db
structure), but the following is what you want.

The form values
<cfset varFrom = DateFormat(Form.FromDate,"mm/dd/yyyy") & Form.FromPeriod>
<cfset varThru = DateFormat(Form.ThruDate,"mm/dd/yyyy") & Form.ThruPeriod>

The where clause

WHERE Convert(varchar(10), DateEnding, 101) + Period BETWEEN '#varFrom#' AND
'#varThru#'

The convert function converts the date to a string. The "101" is the format
this string takes (in this case "mm/dd/yyyy")
Look at the sql server BOL for the convert function to see the other formats
this can have.

The problem is that you are now doing this between on a string value (so there
could be inconsistant results)

Ken