Given the date "03/13/2010" using this in the MYSQL Where section?

Question

I would like to pass the MYSQL query through Coldfusion the following date: 03/13/2010 Therefore, the query filters it like this:

SELECT *
FROM myTable
WHERE dateAdded before or on 03/13/2010

I would also like to have 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 request that can handle both BOTH Date_Start and Date_END, or just one of two?

thank

Answer 1

cfqueryparam cfsqltype CF_SQL_TIMESTAMP. "startDate", "endDate", . startDate endDate - false, :

<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>

, startDate endDate false, , , :

<cfset startDate = false />
<cfquery name="q" datasource="#request.dsn#">....

Answer 2

YYYY-MM-DD Coldfusion, STR_TO_DATE MySQL :

SELECT 
    *
FROM 
    myTable 
WHERE 
    dateAdded <= STR_TO_DATE('03/13/2010','%m/%d/%Y');

...

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');

Answer 3

Coldfusion - cfqueryparam , SQL-.

<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>

Answer 4

SELECT *
FROM myTable 
WHERE dateAdded <= YourdateValue

SELECT *
FROM myTable 
WHERE dateAdded BETWEEN YourdateValueStart AND YourdateValueEnd

STR_TO_DATE (str, )

DATE_FORMAT(). string str . STR_TO_DATE() DATETIME , , . , datetime, str, , STR_TO_DATE() NULL . scans str . , %. . . , , . DATE_FORMAT() .

SELECT * 
FROM myTable  
WHERE (dateAdded <= YourdateValueStart AND YourdateValueEnd  IS NULL)
OR  (dateAdded >= YourdateValueEnd AND YourdateValueStart  IS NULL)
OR (dateAdded BETWEEN YourdateValueStart AND YourdateValueEnd)

Answer 5

I believe in MySQL that you can use comparison operators in 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 is a list of date manipulation functions that mySQL offers: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.htm