problems with dates etc 1

[OccasionalCoder]

I've got a table with first_name, Last_name, Start_year, End_year, TwoStart_year, TwoEnd_year.

I am passing through the url a 4 digit number representing the year.

The url.year can be either a start_year, or a twostart_year.

I tried:

<cfquery name="get_pastfellows" datasource="#db#" username="#un#" password="#pw#">
select * from copyfellows
where end_year is not null and start_year = '#url.year#'
order by last_name
</cfquery>
<cfquery name="get_two" dbtype="query">
select * from get_pastfellows
where twoend_year is not null and twostart_year = '#url.year#'
</cfquery>

I need to convert the url.year (4 digit string) into a date and have it output the name information when either case (start_year or twostart_year) is defined in the url. I tried using createdate and dateformat and nothing seems to work.

I'm relatively inexperienced at this and have tried everything I can think of but it doesn't work.

I greatly appreciate any help.

 

[ecobb]

Always use cfqueryparam when passing variables to a query. It makes it way more secure and it takes care of little problems like this.

<cfquery name="get_pastfellows" datasource="#db#" username="#un#" password="#pw#">
select * from copyfellows
where end_year is not null and start_year = <cfqueryparam value="#url.year#" cfsqltype="CF_SQL_DATE">
order by last_name
</cfquery>
<cfquery name="get_two" dbtype="query">
select * from get_pastfellows
where twoend_year is not null and twostart_year = <cfqueryparam value="#url.year#" cfsqltype="CF_SQL_DATE">
</cfquery>

Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[OccasionalCoder]

I've simplified the query to:

<cfquery name="get_pastfellows" datasource="#db#" username="#un#" password="#pw#">
select * from copyfellows
where end_year is not null and start_year = <cfqueryparam value="#url.year#" cfsqltype="CF_SQL_DATE">
order by last_name
</cfquery>

But I get no data returned. Is there someway that the url.year has to be formatted so that the db understand that the date represents a year rather than ddmm?

Thanks again.

 

[OccasionalCoder]

I'm getting really confused on this...It's probably pretty simple, but I've been looking at it a long time.

Here's my table:

Last_name First_name Start_year End_year Twostart_year Twoend_year
Mertz Fred 05/10/1989 05/11/1990
Norton Ed 04/04/1990 06/20/2004
Arnaz Desi 02/24/1999 03/20/2001 05/03/2002 07/03/2004

What I want is when the date link for 2004 is clicked (passing the string 2004), the data shows up for everyone who ended on the 2004 end_year date or 2004 twoend_year date. When I use the cfqueryparam in the query, it returns nothing.

Thanks

 

[ecobb]

Try this:

<cfquery name="get_pastfellows" datasource="#db#" username="#un#" password="#pw#">
select * from copyfellows
where end_year is not null and [red]year(start_year)[/red] = <cfqueryparam value="#url.year#" cfsqltype="CF_SQL_DATE">
order by last_name
</cfquery>

All I did was put in a Year() function. You need to tell the database that you only want to compare years. The way you had it originally, the query would read "Where 07/03/2004 = 2004".


Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[OccasionalCoder]

Thanks, Ecar, for the help.

This is really peculiar, though.

Using your code above, when the link that passes the date is clicked, the data presented is off by two years, i.e.

I clicked on 1998 and I get the data for 1996. It works this way for every date I have listed. 2000 gives you data for 1998, 2004 for 2002, etc.

I double checked the date passed in the url and it's correct.

 

[OccasionalCoder]

I've got it to work ok....thanks very much for the help, Ecar