Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Another Newbie Problem
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99752
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
Author: Dina Hess
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99628
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
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99590
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
Author: Andy Ewings
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99579
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.***
Author: Kennerly, Rick H CIV
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99577
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 -----
Author: Luce, Greg
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99572
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.***
Author: Dina Hess
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99571
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
Author: Jochem van Dieten
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99554
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
Author: Luce, Greg
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99521
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?
Author: Everett, Al
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99519
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 -----
Author: Kennerly, Rick H CIV
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99510
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?
Author: Tim Laureska
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:19673#99508
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
