一、将默认日期格式'yyyy-MM-dd HH:mm:ss'截取成年、月、日、时、分、秒:
1.EXTRACT
如输入SELECT CURRENT_TIMESTAMP;获取当前时间‘2019-08-16 15:21:28.413’,使用EXTRACT截取后获得的是数字格式的年月日等。
SELECT CURRENT_TIMESTAMP,
EXTRACT(YEAR FROM CRRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CRRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CRRENT_TIMESTAMP) AS day,
EXTRACT(MINUTE FROM CRRENT_TIMESTAMP) AS minute,
EXTRACT(SECOND FROM CRRENT_TIMESTAMP) AS second;
2. select year/month/day/hour/minute/second/week of year
如,select year('2017-02-08 11:15:50'),month('2017-02-08 11:15:50'),day('2017-02-08 11:15:50'),hour('2017-02-08 11:15:50'),minute('2017-02-08 11:15:50'),second('2017-02-08 11:15:50'),weekofyear('2017-02-08 11:15:50');
返回的是 2017、2、8、11、15、50、6
3. to_date
select to_date('2017-02-08 11:15:50'); 截取指定日期时