Another Newbie Problem

Rick... I'm relatively new to CF myself, but managed to develop a calendar app that does among other things, the two variable query you reference... keep in mind, the solutions are not always "laid out" nicely to see In the situation you reference (the month/year scenario), I set up a database that includes numeric fields for each of the year and month.  I set up drop down lists to pass the variables to a query template (as you indicate), then do a query on them ... something like <cfquery datasource="dates" name="cal"> SELECT  * FROM  events e, category c where (e.firstmonth=#firstmonth# and e.firstyear=#firstyear#) and (e.category=c.cat_no or e.category2=c.cat_no order by event_no, firstyear </cfquery> check out   http://208.249.126.159/calendar/webcalendar_first.cfm T In all my cfm references I cannot find a single example of a common task, although you see this kind of thing being used everywhere. What I want to do is select from a pair of drop down lists (like Month and Year) and then display only the records from November 2002, for instance.   How do you pass just those two variables to a query and draw just the date and month from a standard db date/time string.  Is it a datepart function?   I just don't get it.  Any insight or pointing toward a tutorial would be appreciated.   humbled by cfm yet again.... Rick

Thanks for the response and nice product, but we're dealing with a legacy db with thousands of records.  Besides, while what you propose is certainly a work around, is this the most "elegant" or programmerly solution?   Are there other ways of doing this?

Here's how I would do it: Given two form variables, Form.dataMonth and Form.dataYear I would create the start and end dates and use those: <cfset startDate=CreateDate(Form.dataYear, Form.dataMonth, 1)> <cfset endDate=CreateDate(Form.dataYear, Form.dataMonth, DaysInMonth(startDate))> <cfquery name="qDateRange" datasource="#DSN#"> SELECT   COLUMN1, COLUMN2, COLUMN3 FROM   TABLENAME WHERE   DATECOLUMN >= #CreateODBCDate(startDate)#   AND   DATECOLUMN <= #CreateODBCDate(endDate)# </cfquery> Depending on how your database stores dates you may be better off incrementing the endDate by one day and using less than instead of less than or equal to. This is because the endDate is midnight of that date. Anything where the timestamp is after midnight the same day (e.g., 11/30/2002 09:00:00) won't be found. If you're only looking for months, you could do something like this: <cfset endDate=DateAdd("m",1,startDate)> Then your WHERE clause would look like this: DATECOLUMN >= #CreateODBCDate(startDate)# AND DATECOLUMN < #CreateODBCDate(endDate)# If you don't want to be locked in to just looking at a month: <cfset endDate=DateAdd("d",1,CreateDate(Form.dataYear, Form.dataMonth, DaysInMonth(startDate)))> ----- Excess quoted text cut - see Original Post for more -----

Rick,   Can you explain your situation a little more in depth? There are datepart() functions that allow us to compare parts of dates. Greg Thanks for the response and nice product, but we're dealing with a legacy db with thousands of records.  Besides, while what you propose is certainly a work around, is this the most "elegant" or programmerly solution?   Are there other ways of doing this?

Quoting rick@mouseherder.com: > > We have several thousand records in a db with a standard odbc > datetime stamp column.  We want to be able to filter out end of month > statistics (all trouble calls in November, 2002, for instance) by > using a set of drop down menus (month, year, type of call).   There are 2 generic ways to do this: - create a startdate and an enddate and do a BETWEEN search - use SQL date functions to extract year and month from the field and do an exact search on that Code example of the latter (the former has been posted already): SELECT   * FROM     table WHERE    EXTRACT(YEAR FROM datefield) = #year#     AND  EXTRACT(MONTH FROM datefield) = #month# (If your database hasn't implemented SQL date functions the exact syntax might be different and you should naturally use cfqueryparam.) The question which approach is most efficient is highly dependent on the database you are using, and especially the index functionality. I would probably create some indexes and do timings on them. Specifically: CREATE INDEX test1_idx ON table (datefield); CREATE INDEX test2_idx ON table (EXTRACT(YEAR FROM datefield), EXTRACT(MONTH FROM datefield), calltype); If your database supports it, play around with index types (try an RTREE for test1_idx). But unless you have millions of records, speed difference is probably neglegible, and you should go for the strategy that uses the smallest index. Jochem

Well, Jochem, has already given you some valuable information but since Month() and Year() are standard SQL functions, this should work, too: where month(datefield) = #form.month# and year(datefield) = #form.year# ~Dina   Quoting rick@mouseherder.com:   >   > We have several thousand records in a db with a standard odbc   > datetime stamp column.  We want to be able to filter out end of month   > statistics (all trouble calls in November, 2002, for instance) by   > using a set of drop down menus (month, year, type of call).     There are 2 generic ways to do this:   - create a startdate and an enddate and do a BETWEEN search   - use SQL date functions to extract year and month from the field and do   an exact search on that   Code example of the latter (the former has been posted already):   SELECT   *   FROM     table   WHERE    EXTRACT(YEAR FROM datefield) = #year#       AND  EXTRACT(MONTH FROM datefield) = #month#   (If your database hasn't implemented SQL date functions the exact syntax   might be different and you should naturally use cfqueryparam.)   The question which approach is most efficient is highly dependent on the   database you are using, and especially the index functionality. I would   probably create some indexes and do timings on them. Specifically:   CREATE INDEX test1_idx ON table (datefield);   CREATE INDEX test2_idx ON table (EXTRACT(YEAR FROM datefield),   EXTRACT(MONTH FROM datefield), calltype);   If your database supports it, play around with index types (try an RTREE   for test1_idx). But unless you have millions of records, speed   difference is probably neglegible, and you should go for the strategy   that uses the smallest index.   Jochem

Dina Hess wrote: > Well, Jochem, has already given you some valuable information but since > Month() and Year() are standard SQL functions Maybe they work in some non-standard implementation, but that does not make them standard functions. There is no reference to a Month() or a Year() function in SQL:1999. Jochem

Thank you for the clarification, Jochem. Actually, any attempt to apply the word "standard" to the many flavors of SQL probably *is* questionable. :) It may be more appropriate to state that the Month() and Year() functions are available for use in SQL Server's Transact-SQL, Oracle's PL/SQL, and MS Access's Jet SQL. But I'm unfamiliar with SQL:1999. A quick check on google seems to indicate this is an attempt to provide a SQL standard. Care to comment further on that? ~Dina   Dina Hess wrote:   > Well, Jochem, has already given you some valuable information but since   > Month() and Year() are standard SQL functions   Maybe they work in some non-standard implementation, but that does not   make them standard functions. There is no reference to a Month() or a   Year() function in SQL:1999.    Jochem

Dina Hess wrote: > > Actually, any attempt to apply the word "standard" to the many flavors of SQL probably *is* questionable. :) Not at all. There have been official SQL standards since 1987. You seem to confuse the lack of conforming implementations with the absence of a standard. > But I'm unfamiliar with SQL:1999. A quick check on google seems to indicate this is an attempt to provide a SQL standard. Care to comment further on that? SQL:1999 is the short name for ISO/IEC 9075:1999. That is the fourth version of the SQL standard, after SQL-87, SQL-89 (a.k.a. SQL1) and SQL-92 (a.k.a. SQL2). SQL:1999 is sometimes refered to as SQL3. (Never mind the difference in notation/numbering, they all have reasons that are mostly due to who gets to write the standard.) Part 2 of the standard, Foundation (SQL/Foundation), sets the standards for what is commonly termed SQL. Amongst other it defines EXTRACT (6.17) with the syntax: <extract expression> ::=   EXTRACT <left paren> <extract field>     FROM <extract source> <right paren> <extract field> is year, month, day, hour etc. <extract source> is a date/time/interval. I don't have any versions of the standard older as SQL-92, but EXTRACT was in it then. So in the case of SQL Server's Transact-SQL, for 10 years they have neglected to implement the functionality by aliassing EXTRACT to the Year() and Month() functions which apparently are present. BTW, MONTH and YEAR are both reserved words in SQL:1999 (5.2). Maybe they need some more customers to express their dissatisfaction with the lack of standards compliance which hinders their code portability. The SQL standard is available from your national standards body or libraries. If you are good at online searching and know the right keywords you might find a free copy online on some Russian server. Jochem

If you have a search form with a month select and a year selectbox. And you're searching for records with call_date newer than the selected values. <cfset compare_date = "#createdate(form.year_search,form.month_search,01)#"> SELECT * FROM tblTable WHERE call_date > '#compare_date#' Rick,   Can you explain your situation a little more in depth? There are datepart() functions that allow us to compare parts of dates. Greg Sure, thanks for the interest. We have several thousand records in a db with a standard odbc datetime stamp column.  We want to be able to filter out end of month statistics (all trouble calls in November, 2002, for instance) by using a set of drop down menus (month, year, type of call).   This is the kind of product one sees everywhere on the internet, but I just can't find a reference piece to get my head in the right place about how to go about it.  Plus, as I've learned, there are always several approaches to the same problem.   Any ideas are appreciated.   Rick ***Sterling Financial Investment Group, Inc. (SFIG) is a member of NASD/MSRB/NFA/SIPC.  Email transmissions may be monitored.  SFIG cannot accept orders to buy or sell via email.  Please visit www.mysterling.com for more information.***

Brillant! That did it.  Thanks to all who assisted, particular Al.   Rick Here's how I would do it: Given two form variables, Form.dataMonth and Form.dataYear I would create the start and end dates and use those: <cfset startDate=CreateDate(Form.dataYear, Form.dataMonth, 1)> <cfset endDate=CreateDate(Form.dataYear, Form.dataMonth, DaysInMonth(startDate))> <cfquery name="qDateRange" datasource="#DSN#"> SELECT   COLUMN1, COLUMN2, COLUMN3 FROM   TABLENAME WHERE   DATECOLUMN >= #CreateODBCDate(startDate)#   AND   DATECOLUMN <= #CreateODBCDate(endDate)# </cfquery> Depending on how your database stores dates you may be better off incrementing the endDate by one day and using less than instead of less than or equal to. This is because the endDate is midnight of that date. Anything where the timestamp is after midnight the same day (e.g., 11/30/2002 09:00:00) won't be found. If you're only looking for months, you could do something like this: <cfset endDate=DateAdd("m",1,startDate)> Then your WHERE clause would look like this: DATECOLUMN >= #CreateODBCDate(startDate)# AND DATECOLUMN < #CreateODBCDate(endDate)# If you don't want to be locked in to just looking at a month: <cfset endDate=DateAdd("d",1,CreateDate(Form.dataYear, Form.dataMonth, DaysInMonth(startDate)))> ----- Excess quoted text cut - see Original Post for more -----

I personally donnever create date objects in CF and pass them into SQL - can cause all sorts of probs with the interpretation  depending on your SQL settings.  what I'd do is create a date in the format dd/mm/yyyy as a character string and then run SET DATEFORMAT DMY before your select statement...........but then that's just me. If you have a search form with a month select and a year selectbox. And you're searching for records with call_date newer than the selected values. <cfset compare_date = "#createdate(form.year_search,form.month_search,01)#"> SELECT * FROM tblTable WHERE call_date > '#compare_date#' Rick,   Can you explain your situation a little more in depth? There are datepart() functions that allow us to compare parts of dates. Greg Sure, thanks for the interest. We have several thousand records in a db with a standard odbc datetime stamp column.  We want to be able to filter out end of month statistics (all trouble calls in November, 2002, for instance) by using a set of drop down menus (month, year, type of call).   This is the kind of product one sees everywhere on the internet, but I just can't find a reference piece to get my head in the right place about how to go about it.  Plus, as I've learned, there are always several approaches to the same problem.   Any ideas are appreciated.   Rick ***Sterling Financial Investment Group, Inc. (SFIG) is a member of NASD/MSRB/NFA/SIPC.  Email transmissions may be monitored.  SFIG cannot accept orders to buy or sell via email.  Please visit www.mysterling.com for more information.***