Given a Date "03/13/2010", using that in a MYSQL Where Clause?
I would like to pass a MYSQL query via Coldfusion th开发者_JAVA百科e following date: 03/13/2010 So the query filters against it like so:
SELECT *
FROM myTable
WHERE dateAdded before or on 03/13/2010
I'd also like to be able to take 2 dates as ranges, from: 01/11/2000, to: 03/13/2010
SELECT *
FROMT myTable
WHERE dateAdded is ON or Between 01/11/2000 through 03/13/2010
Also, Is there a way to have one query that can handle either BOTH Date_Start and Date_END, or just one of the two?
thanks
If you cannot format your date to YYYY-MM-DD
in Coldfusion, you can use the STR_TO_DATE
function in MySQL as follows:
SELECT
*
FROM
myTable
WHERE
dateAdded <= STR_TO_DATE('03/13/2010','%m/%d/%Y');
And...
SELECT
*
FROM
myTable
WHERE
dateAdded BETWEEN STR_TO_DATE('01/11/2000','%m/%d/%Y') AND
STR_TO_DATE('03/13/2010','%m/%d/%Y');
The best way to do it in Coldfusion would be to use cfqueryparam to manage the translation of the string to a date object as well as providing a level of SQL injection prevention.
<cfset myResult = queryNew("id")>
<cfset startDate = "01/11/2000">
<cfset endDate = dateFormat(now(), "mm/dd/yyyy")>
<cfquery name="myResult" datasource="myDatasource">
SELECT *
FROM myTable
WHERE dateAdded BETWEEN <cfqueryparam CFSQLType="CF_SQL_DATE" value="#startDate#">
AND <cfqueryparam CFSQLType="CF_SQL_DATE" value="#endDate#">
</cfquery>
<cfoutput query="myResult">
...output code here
</cfoutput>
SELECT *
FROM myTable
WHERE dateAdded <= YourdateValue
or
SELECT *
FROM myTable
WHERE dateAdded BETWEEN YourdateValueStart AND YourdateValueEnd
Have a look at STR_TO_DATE(str,format)
This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning. The server scans str attempting to match format to it. The format string can contain literal characters and format specifiers beginning with %. Literal characters in format must match literally in str. Format specifiers in format must match a date or time part in str. For the specifiers that can be used in format, see the DATE_FORMAT() function description.
SELECT *
FROM myTable
WHERE (dateAdded <= YourdateValueStart AND YourdateValueEnd IS NULL)
OR (dateAdded >= YourdateValueEnd AND YourdateValueStart IS NULL)
OR (dateAdded BETWEEN YourdateValueStart AND YourdateValueEnd)
You can use cfqueryparam with a cfsqltype of CF_SQL_TIMESTAMP. Given both a "startDate" and "endDate" variable the below code should work. If either startDate or endDate is set to something like false then it won't be included:
<cfset startDate = createDate(2010, 01, 19) />
<cfset endDate = createDate(2010, 01, 26) />
<cfquery name="q" datasource="#request.dsn#">
SELECT *
FROM myTable
WHERE
(
<cfif isDate(startDate)>
dateAdded >= <cfqueryparam value="#dateFormat(startDate, "YYYY/MM/DD")#" cfsqltype="CF_SQL_TIMESTAMP" />
</cfif>
<cfif isDate(endDate)>
<cfif isDate(startDate)>AND</cfif>
dateAdded <= <cfqueryparam value="#dateFormat(endDate, "YYYY/MM/DD")#" cfsqltype="CF_SQL_TIMESTAMP" />
</cfif>
)
</cfquery>
Try running the same thing, but with either startDate or endDate set to false such as below to see how it all works in a single query:
<cfset startDate = false />
<cfquery name="q" datasource="#request.dsn#">....
I believe in MySQL you can use comparison operators on dates
so I think you can
SELECT *
FROM myTable
WHERE dateAdded <= 20100111
and
SELECT *
FROMT myTable
WHERE dateAdded is BETWEEN 20000111 AND 20100303
In general, here's a list of date manipulation functions that mySQL offers: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.htm
精彩评论