Oracle与时间相关的查询 & 修改系统时间格式

【to_char()将时间值转换为字符串，to_date()将字符串转换为时间】

select customer_id,first_name||''||last_name,dob from customers where to_char(dob,'mm')='07';
select customer_id,first_name||''||last_name,dob from customers where to_char(dob,'yyyy')='1958';
select customer_id,first_name||''||last_name,dob from customers where to_char(dob,'yyyy-mm')='1958-04';
select customer_id,first_name||''||last_name,dob from customers where to_char(dob,'yyyy-mm-dd')='1958-04-13';
select customer_id,first_name||''||last_name,dob from customers where to_char(dob,'yyyy-mm-dd')>'1958-04-13';
select customer_id,first_name||''||last_name,dob from customers where dob=to_date('1958-4月-13','yyyy-month-dd');
select customer_id,first_name||''||last_name,dob from customers where dob>to_date('1958-4月-13','yyyy-month-dd');
select customer_id,first_name||''||last_name,dob from customers where dob>to_date('1958-4月','yyyy-month');

2、修改系统时间格式

alter session set NLS_DATE_FORMAT='YYYY-MM-DD-hh24-mi-ss';

select sysdate-1/24,sysdate-1/1440,sysdate-1/86400,sysdate from dual;