日期字符串在日期范围select语句中不起作用 [英] Date strings not working in date range select statement

问题描述

我有一个问题，有几个日期，暂时没有我。

I'm having an issue with a couple of dates which is eluding me at the moment.

我有两个日期字符串，格式如下： dd-mm-yyyy 。例如

I have 2 date strings in the following format: dd-mm-yyyy. So for example

• DATE1 = 10-03-2013


• DATE2 = 15-03-2013

• DATE1 = 10-03-2013
• DATE2 = 15-03-2013

I需要在以下查询中使用这些日期：

I need to use these dates in the following query:

<cfquery name="qry1" datasource="#getDSN()#">
        SELECT      
            DOCUMENT_NUMBER
        FROM
            MY TABLE            
        WHERE
            DATE_ORDER_ENTER 
        BETWEEN 
            <cfqueryparam value="#DATE1#"  maxlength="10">
        AND
            <cfqueryparam value="#DATE2#" maxlength="10">                   
    </cfquery>

我遇到的问题是将这些日期字符串转换为实际日期。我发现月和日都在交换。我已经尝试像 createODBCDate（）的功能，看看是否治愈了问题，但它仍然不工作。有人有解决方案吗？

The problem I am having is converting these date strings to actual dates. I am finding the month and day are swapping around. I have tried functions like createODBCDate() to see if that cured the problem, but it is still not working. Does anyone have a solution?

我使用的是CF9.01和Oracle 10g。

I am using CF9.01 and Oracle 10g.

b $ b JC

推荐答案

我发现月份和日子正在交换。我尝试
函数，如createodbcdate（），看看这将解决问题
，但仍然不工作。

I am finding the month and day are swapping around. I have tried functions like createodbcdate() to see if this will cure the problem but still not working.

标准CF日期函数如CreateODBCDate，ParseDateTime，etecetera 始终使用英文（美国）日期格式约定。因此，日期字符串 10-03-2013 将始终视为10月3日。你可以运行非模糊的日期字符串，如 15-03-2012 。显然没有月15，所以CF自动交换他们。

The standard CF date functions like CreateODBCDate, ParseDateTime, etecetera always use English (U.S.) date formatting conventions. So the date string 10-03-2013 will always be treated as October 3rd. You get lucky with non-ambiguous date strings like 15-03-2012. Obviously there no month 15, so CF automatically swaps them. That is why it seems to work with some non-US date strings, but not others.

要解析区域设置的日期字符串，请使用

To parse locale sensitive dates, use the LSxxxxx date functions as @duncan suggested. Ignoring validation, something like this:

   <cfset dateObject = LSParseDateTime("10/03/2013", "pt_PT")>

请注意，根据文档破折号，即dd-mm-yyyy在大多数其他语言环境中不是标准的。显然只有荷兰语（标准）和葡萄牙语（标准）。

Note, according to the documentation the dashes ie dd-mm-yyyy are NOT standard in most other locales. Apparently only in Dutch(Standard) and Portuguese (Standard).

此外，请务必将正确的日期类型添加到 cfqueryparam as @Dan建议。否则，它将默认键入 char ，并可能导致语法错误。

Also, be sure to add the correct date type to cfqueryparam as @Dan suggested. Otherwise, it will default to type char and may cause a syntax error.

这篇关于日期字符串在日期范围select语句中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！