Informix SQL：如何获取查询中日期时间字段的日期部分？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4178169/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Informix SQL: How to get the date part of a datetime field in a query?
提问by weismat
What is the best way to use a only the date part of a datetime field in a query?
I have a datetime field and want to group/count it by date.
在查询中仅使用日期时间字段的日期部分的最佳方法是什么？
我有一个日期时间字段，想按日期对其进行分组/计数。
回答by Jonathan Leffler
There are a number of ways of doing it:
有多种方法可以做到：
EXTEND(dt_field, YEAR TO DAY)
CAST(dt_field AS DATETIME YEAR TO DAY)
dt_field::DATETIME YEAR TO DAY
CAST(dt_field AS DATE)
dt_field::DATE
DATE(dt_field)
The simplest - as in shortest - are the last two, and the function notation is probably clearest. The first three leave you with a DATETIME value; the last three leave you with a DATE value. These are similar, but not identical. They are fairly freely interchangeable though.
最简单的——简而言之——是最后两个，函数符号可能是最清楚的。前三个给你一个 DATETIME 值；最后三个给你一个 DATE 值。这些是相似的，但不完全相同。不过，它们可以相当自由地互换。
回答by AndreKR
date(name_of_field)
回答by Kayathiri
This
这个
to_char(dt_field, "%d %B %Y")
gives as 31 May 2016.
作为31 May 2016.
To group:
分组：
SELECT YEAR(dt_field) year, MONTH(dt_field) month, COUNT(*) count
FROM tblName
GROUP BY 1, 2
ORDER BY 1, 2;
回答by Paul
Just to add to these...
只是为了补充这些......
I was looking for some way to extract parts of dates and times in Informix, and found these, just in case they're useful to anyone, they're all here in one place.
我一直在寻找某种方法来提取 Informix 中的部分日期和时间，并找到了这些，以防万一它们对任何人有用，它们都在一个地方。
As text...
作为文字...
TO_CHAR(<value>, "%H")Hours (00 - 23)TO_CHAR(<value>, "%M")Minutes (00 - 59)TO_CHAR(<value>, "%S")Seconds (00 - 61)
TO_CHAR(<value>, "%H")小时 (00 - 23)TO_CHAR(<value>, "%M")分钟 (00 - 59)TO_CHAR(<value>, "%S")秒 (00 - 61)
%aAbbreviated weekday name, as defined in the locale%bAbbreviated month name, as defined in the locale%CThe century number (the year divided by 100 and truncated to an integer) as an integer (00 through 99)%DThe same as the %m/%d/%y format%eDay of the month as a number (1 through 31). A single-digit value is preceded by a blank space.%FnThe value of the fraction of a second, with precision specified by the unsigned integer n. The default value of n is 2; the range of n is 0 ≤ n ≤ 5. This value overrides any width or precision that is specified between the % and F characters.%hSame as the %b format: abbreviated month name, as defined in the locale%HHour as a 2-digit integer (00 through 23) (24-hour clock)%IHour as a 2-digit integer (00 through 11) (12-hour clock)%mMonth as an integer (01 through 12). Any single-digit value is preceded by a zero (0).%MMinute as a 2-digit integer (00 through 59)%SSecond as a 2-digit integer (00 through 61). The second value can be up to 61 (instead of 59) to allow for the occasional leap second and double leap second.%TTime in the %H:%M:%S format%wWeekday as a number (0 through 6); 0 represents the locale equivalent of Sunday.%yYear as a 2-digit decimal number.
%a缩写的工作日名称，在语言环境中定义%b缩写的月份名称，在语言环境中定义%C世纪数（年份除以 100 并截断为整数）作为整数（00 到 99）%D与 %m/%d/%y 格式相同%e以数字表示的月份中的第几天（1 到 31）。一位数值前面有一个空格。%Fn秒的小数部分的值，精度由无符号整数 n 指定。n 的默认值为 2；n 的范围是 0 ≤ n ≤ 5。该值会覆盖在 % 和 F 字符之间指定的任何宽度或精度。%h与 %b 格式相同：缩写的月份名称，在语言环境中定义%H作为 2 位整数的小时（00 到 23）（24 小时制）%I作为 2 位整数的小时（00 到 11）（12 小时制）%m整数形式的月份（01 到 12）。任何一位数字值前面都有一个零 (0)。%M分钟作为 2 位整数（00 到 59）%S第二个是 2 位整数（00 到 61）。第二个值最多可以是 61（而不是 59），以允许偶尔出现闰秒和双闰秒。%T%H:%M:%S 格式的时间%w工作日作为数字（0 到 6）；0 表示相当于星期日的语言环境。%y年份为 2 位十进制数。
As integers...
作为整数...
DAY(<value>)Day of month (0 - month max)MONTH(<value>)Month of year (1 - 12)YEAR(<value>)Year number
DAY(<value>)一个月中的哪一天（0 - 最大月）MONTH(<value>)一年中的月份 (1 - 12)YEAR(<value>)年数
