给定日期“03/13/2010”，在MYSQL Where Clause中使用该日期？ [英] Given a Date "03/13/2010", using that in a MYSQL Where Clause?

问题描述

我想通过Coldfusion在以下日期传递MYSQL查询：03/13/2010
所以查询过滤如下：

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

我也希望能够将2个日期作为范围，从：01/11/2000到：03/13/2010

  SELECT * 
 FROMT myTable 
 WHERE dateAdded为ON或介于01/11/2000至03/13/2010 
  

另外，有没有办法有一个查询可以处理BOTH Date_Start和Date_END，或只是其中一个？ p>

谢谢

解决方案

您可以将cfqueryparam与CF_SQL_TIMESTAMP的cfsqltype配合使用。
给定startDate和endDate变量，以下代码应该可以工作。如果startDate或endDate设置为false，那么它将不被包含：

 < cfset startDate = createDate 2010，01,19）/> 
< cfset endDate = createDate（2010,01,26）/> 
 
< cfquery name =qdatasource =＃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 =qdatasource =＃request.dsn＃> .... 
  

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

解决方案

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

这篇关于给定日期“03/13/2010”，在MYSQL Where Clause中使用该日期？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！