选择Varchar作为日期 [英] Select Varchar as Date

问题描述

我想选择一个varchar字段作为日期字段

I want to select a varchar field as a date field

例如，一个字段的值为"30.12.2011 21:15:03"

For example a field has this value "30.12.2011 21:15:03"

当我选择此选项时

select DATE from TABLE where DATE = '30.12.2011'

我没有结果.

推荐答案

假定该字段为日期字段，请结合使用DATE导引器和yyyy-mm-dd(或同时加上时间的TIMESTAMP).

Assuming the field is a date field, use the DATE introducer combined with yyyy-mm-dd (or TIMESTAMP with time as well).

因此使用:

select datefield from sometable where datefield = DATE '2011-12-30'

从技术上讲，您可以不使用介绍器，但是根据SQL标准，它是更正器".

Technically you can leave off the introducer, but it is 'correcter' in the light of the SQL standard.

假设为TIMESTAMP字段，除非时间戳始终(始终)为00:00:00.0000(在这种情况下，它本来应该是DATE)，否则您将不会获得结果.

Assuming a TIMESTAMP field, you won't get results unless the timestamp is (always) at 00:00:00.0000 (in which case it should have been a DATE instead).

为了进行比较，您需要使用BETWEEN之一，例如:

For the comparison to work, you need to use either BETWEEN, eg:

select timestampfield from sometable 
where timestampfield BETWEEN '2011-12-30 00:00:00.0000' AND '2011-12-30 23:59:59.9999'

或将时间戳截断为某个日期(如果为索引加了索引，这可能会对性能产生不利影响，因为这样便无法再使用索引了)，例如:

or truncate the timestamp to a date (this may adversely effect performance if the timestamp is indexed, because then the index can no longer be used), eg:

select timestampfield from sometable
where CAST(timestampfield AS DATE) = '2011-12-30'

如果日期存储在VARCHAR字段中(这本身是个坏主意)，则有几种解决方案，首先是将其作为日期处理来处理:

If the date is stored in a VARCHAR field (which in itself is a bad idea), there are several solutions, first is to handle it as date manipulation:

select varcharfield from sometable
where CAST(CAST(varcharfield AS TIMESTAMP) AS DATE) = '2011-12-30'

如果在VARCHARFIELD中也有时间分量，则必须进行两次强制转换.假定日期采用下面列出的受支持格式.如果您如上所述使用BETWEEN，则可以使用单个强制转换进行时间戳记

The double cast is required if you have a time-component in VARCHARFIELD as well. This assumes dates in the supported format listed below. If you use BETWEEN as above, you can use a single cast to timestamp)

另一种解决方案(如hvd建议的 )是将其纯粹视为字符串操作，例如:

The other solution (as suggested by hvd) is to treat it purely as string manipulation, for example:

select varcharfield from sometable
where varcharfield STARTING WITH '30.12.2011'

如果要选择范围，这会有其自身的一系列问题.底线:使用真实的TIMESTAMP字段！

This has its own set of problems if you want to select ranges. Bottomline: use a real TIMESTAMP field!

请注意，Firebird支持多种格式:

Note that Firebird supports multiple formats:

• yyyy-mm-dd，例如2014-05-25(ISO-8601格式，最好使用，因为它可以减少混乱)
• dd.mm.yyyy，例如2014年5月25日
• mm/dd/yyyy，例如2014年5月25日
• mm-dd-yyyy，例如2014年5月25日
• dd mmm yyyy，例如2014年5月25日(+以-，.或/作为分隔符的变体形式)
• mmm dd yyyy，例如2014年5月25日(+以-，.或/作为分隔符的变体形式)

• yyyy-mm-dd, eg 2014-05-25 (ISO-8601 format, probably best to use as it reduces confusion)
• dd.mm.yyyy, eg 25.05.2014
• mm/dd/yyyy, eg 05/25/2014
• mm-dd-yyyy, eg 05-25-2014
• dd mmm yyyy, eg 25 MAY 2014 (+ variations with a -, . or / as separator)
• mmm dd yyyy, eg MAY 25 2014 (+ variations with a -, . or / as separator)

这篇关于选择Varchar作为日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！