Getting Date Range, then put into database as dates by comma delimited

[wrighterb]

I take two dates from the db start and end, and find all dates in this range, and return so

09/02/2007,09/03/2007,09/04/2007,09/05/2007,09/06/2007,09/07/2007,09/08/2007

Then I grab what is in already in the field that I want to combine this with.

11/15/2007,10/25/2007,10/26/2007,10/27/2007

To make

11/15/2007,10/25/2007,10/26/2007,10/27/2007,09/02/2007,09/03/2007,09/04/2007,09/05/2007,09/06/2007,09/07/2007,09/08/2007
Comma Delimited dates, then put it back into the database.

How can I do this, this is what I have so far, but I get a string and put a string back into the database, not comma delimited dates.

<cfquery name="getRecord" datasource="#request.dsn#">
SELECT TOP 1 *
From Reservations
WHERE f_name = '#Form.f_name#' and l_name = '#Form.l_name#' and zip = #Form.zip#
ORDER BY ID DESC
</cfquery>
<cfquery name="getThisRecord" datasource="#request.dsn#">
SELECT arrive, depart
From Reservations
WHERE ID = #getRecord.ID#
</cfquery>
<cfoutput>#getThisRecord.arrive# - #getThisRecord.depart#</cfoutput>
<cfquery name="Pending" datasource="#request.dsn#">
Select d_pending
From calendar_dates
WHERE P_ID = #url.ID#
</cfquery>

<cfoutput>
<cfparam default="" name="update">
<cfloop from="#getThisRecord.arrive#" to="#getThisRecord.depart#" index="i">
<cfset update = '#update#,#dateformat(i, "mm/dd/yyyy")#'/>
</cfloop>
<cfset d_pending = '#pending.d_pending##update#' />
#d_pending#
</cfoutput>

Any Ideas?

 

[cfStarlight]

I would strongly suggest you reconsider your design. Storing delimited data is generally considered a bad design. Storing lists of data in varchar fields necessitates that you use awkward/error prone queries that are usually poor performers to boot.

http://en.wikipedia.org/wiki/Database_normalization

 

[wrighterb]

This date string being store in a memo field, so it acts like a text file.

This data is only used to highlight something and its only used for show, no data manipulation, all that is store with primary keys.

Do you know how to?

 

[cfStarlight]

It sounds like the data is being manipulated, via an update I still think its an over-complication but anyway ..

What is the problem with your code? You should be able to use either string or list functions to concatenate 2 lists.

psuedo-code

UPDATE  YourTable
SET     ColumnName = '#listAppend(listOne, listTwo, ",")#'
WHERE   ....

Also, what happens if the dates in the two lists overlap or interect?

 

[cfStarlight]

> interect?

intersect

 

[wrighterb]

Here I can get it to work great,

http://www.wrighter.com/mike/call_caladmin.cfm

But when I try to automate it, it's like the date isn't getting made.

Tried the list append

 

[wrighterb]

Forget it I am going to just have normal tables