一、在数据库查询中,我们会经验遇到日期的对比,在常用数据库中,我们使用最多的是oracle,mysql和db2。
DB2:
此处,通过两个日期相减得出的正负数,可以判断日期大小。
二、字符串截取,查询某字段中值截取后特定索引位置的值。
DB2和Oracle:
mysql:
DB2:
SELECT timestamp('2014-06-11 08:03:20')-timestamp('2014-06-10 08:01:00'),
specialcheck,SPECIAL_FIELD, CURRENT_DATE, EXAMTYPE,
patient_name,record_no,register_date,calldate,
(select swift_number from REGISTER_INFO where
patient_haveroom.record_no=REGISTER_INFO.record_no) as swift_number,substr( (select
swift_number from REGISTER_INFO where
patient_haveroom.record_no=REGISTER_INFO.record_no) ,10,1) as timeStr
FROM patient_haveroom
where record_no='10120140605016249' or record_no='10120140605016247' or
record_no='10120140605016246'
此处,通过两个日期相减得出的正负数,可以判断日期大小。
二、字符串截取,查询某字段中值截取后特定索引位置的值。
DB2和Oracle:
SELECT timestamp(current timestamp)-timestamp('2014-06-11 08:01:00'),
specialcheck,SPECIAL_FIELD, EXAMTYPE,
patient_name,record_no,register_date,calldate,
(select swift_number from REGISTER_INFO where
patient_haveroom.record_no=REGISTER_INFO.record_no) as swift_number,
(select booking_date from REGISTER_INFO where
patient_haveroom.record_no=REGISTER_INFO.record_no) as booking_date,
substr( (select swift_number from REGISTER_INFO where
patient_haveroom.record_no=REGISTER_INFO.record_no) ,10,1) as timeStr
FROM patient_haveroom
where (patient_name='预约1' or patient_name='预约2' or patient_name='预约3' or
patient_name='预约4' or patient_name='test2')
and substr( (select swift_number from REGISTER_INFO where
patient_haveroom.record_no=REGISTER_INFO.record_no) ,10,1)='0'
mysql:
substring(swift_number,10,1)='2'")
除了函数不一样。其它与mysql和oracle一样的。