1.1、将字符串转换成时间格式(to_date)
语法:to_date(需要转换的字符串,字符串对应的格式)
字符串类型:
char:固定长度,0-255个字符,默认值1;
varchar:可变长度,0-65535个字符;
日期类型
1、date 年月日 yyyy-mm-dd
2、time 时分秒 hh:mm:ss
3、year 年 yyyy
4、datetime 年月日时分秒 yyyy-mm-dd hh:mm:ss
5、timestamp 时间戳,混合日期值 yyyymmdd hhmmss
to_date:将字符串转换为日期值
to_date(需要转换的字符串,字符串对应的格式)
select to_date('2022-05-08','yyyy-mm-dd') as da from ap;
update ap set rq=replace(rq,'2022-5-8','2022-05-08') where 条件;
update ap set rq=replace(rq,'2022-0508','2022-05-08') where 条件;
update ap set rq=replace(rq,'2022/05/08','2022-05-08') where 条件;
0513
select to_date('2022-05-08','yyyy-mm-dd') as da from dual; -- 2022-05-08 00:00:00
select to_date('2022-5-8-23-03-56','yyyy-mm-dd-hh24-mi-ss') as da from dual; -- 2022-05-08 23:03:56
select to_date('2022-05/08','yyyy-mm/dd') as da from dual; -- 2022-05-08 00:00:00
select to_date('2022/05/08 23:03:56','yyyy/mm/dd hh24:mi:ss') as da from dual; -- 2022-05-08 23:03:56
0525
用ddsj和to_date方法查询关舱当日数据量,航班架次,及人数
SELECT
count(*) num,
count(DISTINCT(hbh)) hbh,
count(DISTINCT(zjhm)) rs
FROM
JCZB_JGGCRY_INFO
WHERE
to_date(ddsj, 'yyyy-mm-dd hh24:mi:ss') >= to_date('2022-05-25 00:00:00','yyyy-mm-dd hh24:mi:ss')
用ddsj和to_date方法查询关舱昨日数据量,航班架次,及人数
SELECT
count( * ) num,
count( DISTINCT ( hbh ) ) hbh,
count( DISTINCT ( zjhm ) ) rs
FROM
jczb_jggcry_info
WHERE
to_date( ddsj, 'yyyy-mm-dd hh24:mi:ss' ) BETWEEN SYSDATE - 1
AND SYSDATE
用ddsj和to_date方法查询关舱近一周数据量,航班架次,及人数
SELECT
count( * ) num,
count( DISTINCT (hbh)) hbh,
count( DISTINCT (zjhm)) rs
FROM
jczb_jggcry_info
WHERE
to_date( ddsj, 'yyyy-mm-dd hh24:mi:ss' ) BETWEEN SYSDATE - 7
AND SYSDATE