Given a Date "03/13/2010", using that in a MYSQL Where Clause?

Question

I would like to pass a MYSQL query via Coldfusion the 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

Answer 1

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)

Answer 2

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

Answer 3

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

Answer 4

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>

Answer 5

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#">....