在Hive中使用Month()在MM中使用Month [英] Month in MM using Month() in Hive
问题描述
Select * from concat(YEAR(DATE_SUB(MAX(Column_name),60),MONTH(DATE_SUB(MAX(Column_name),60),-01)
month()
在直到9月的几个月内仅产生一位数字,即Jan返回1
而不是01
.在处理此问题时需要帮助.
The month()
yields only single digit for months until September i.e Jan returns 1
instead of 01
. Need help in handling this.
我正在使用此输出使用TO_DATE
馈送给另一个SELECT
查询.
I am using this output to feed to another SELECT
query using TO_DATE
.
推荐答案
month()
函数返回整数,这就是为什么不存在前导零的原因.您可以使用lpad(month,2,0)
函数格式化月份:
month()
function returns integer, that is why there is no leading zero. You can use lpad(month,2,0)
function to format month:
hive> select lpad(month('2017-09-01'),2,0);
OK
09
Time taken: 0.124 seconds, Fetched: 1 row(s)
hive> select lpad(month('2017-10-01'),2,0);
OK
10
Time taken: 0.433 seconds, Fetched: 1 row(s)
或者,您可以使用substr()
从日期中提取年份和月份:
Alternatively you can use substr()
to extract year and month from the date:
hive> select substr('2017-10-01',1,4) as year, substr('2017-10-01',6,2) as month;
OK
year month
2017 10
Hive 2.1.0之前的
date_sub()函数(HIVE-13248)返回类型为String,因为创建方法时不存在Date类型.参见此处: https://cwiki.apache.org/confluence/display/Hive/LanguageManual + UDF
date_sub() function prior to Hive 2.1.0 (HIVE-13248) return type was a String because no Date type existed when the method was created. See here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
这篇关于在Hive中使用Month()在MM中使用Month的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!