oracle学习笔记之日期和时间

1 显示日期

SQL> select sysdate from dual;

SYSDATE
--------------
25-8月 -21

2 修改日期格式

SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
Session altered.

//显示是否修改成功
SQL> select * from v$nls_parameters;
PARAMETER                                             VALUE
------------------------------------------------------------- -----------------------------
NLS_LANGUAGE                                          SIMPLIFIED CHINESE
NLS_TERRITORY                                         CHINA
NLS_CURRENCY                                          ￥
NLS_ISO_CURRENCY                                      CHINA
NLS_NUMERIC_CHARACTERS                                .,
NLS_CALENDAR                                          GREGORIAN
NLS_DATE_FORMAT                                       yyyy-mm-dd
NLS_DATE_LANGUAGE                                     SIMPLIFIED CHINESE
NLS_CHARACTERSET                                      UTF8
NLS_SORT                                              BINARY
NLS_TIME_FORMAT                                       HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT                                  DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                                    HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT                               DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                                     ￥
NLS_NCHAR_CHARACTERSET                                AL16UTF16
NLS_COMP                                              BINARY
NLS_LENGTH_SEMANTICS                                  BYTE
NLS_NCHAR_CONV_EXCP                                   FALSE


ps:alter session 这样的修改只是会话级别的修改

3 再次显示日期

SQL> select sysdate from dual;
SYSDATE
----------
2021-08-25

4 雇佣表中关于日期的筛选例子

//例子1
SQL> select * from emp
  2  where hiredate>'1981-01-01';

	EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
	7499 ALLEN                SALESMAN                 7698 1981-02-20       1600        300         30
	7521 WARD                 SALESMAN                 7698 1981-02-22       1250        500         30
	7566 JONES                MANAGER                  7839 1981-04-02       2975                    20
	7654 MARTIN               SALESMAN                 7698 1981-09-28       1250       1400         30
	7698 BLAKE                MANAGER                  7839 1981-05-01       2850                    30
	7782 CLARK                MANAGER                  7839 1981-06-09       2450                    10
	7788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20
	7839 KING                 PRESIDENT                     1981-11-17       5000                    10
	7844 TURNER               SALESMAN                 7698 1981-09-08       1500          0         30
	7876 ADAMS                CLERK                    7788 1987-05-23       1100                    20
	7900 JAMES                CLERK                    7698 1981-12-03        950                    30
	7902 FORD                 ANALYST                  7566 1981-12-03       3000                    20
	7934 MILLER               CLERK                    7782 1982-01-23       1300                    10


//例子2
SQL> select * from emp
2  where hiredate>'1983-01-01';

	EMPNO ENAME                JOB                       MGR HIREDATE          SAL       COMM     DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
	7788 SCOTT                ANALYST                  7566 1987-04-19       3000                    20
	7876 ADAMS                CLERK                    7788 1987-05-23       1100                    20

5 计算员工入职的天，周，月，年

//粗算的方法
SQL> select ename 员工姓名, (sysdate-hiredate) 天, 
            (sysdate-hiredate)/7 周,(sysdate-hiredate)/30 月,
			(sysdate-hiredate)/365 年
2    from emp;

	员工姓名             天         周         月         年
-------------------- ---------- ---------- ---------- ----------
SMITH                14865.9034 2123.70048 495.530112 40.7285024
ALLEN                14800.9034 2114.41477 493.363446 40.5504202
WARD                 14798.9034 2114.12905 493.296779 40.5449407
JONES                14759.9034 2108.55762 491.996779 40.4380914
MARTIN               14580.9034  2082.9862 486.030112 39.9476805
BLAKE                14730.9034 2104.41477 491.030112 40.3586394
CLARK                14691.9034 2098.84334 489.730112   40.25179
SCOTT                12551.9034 1793.12905 418.396779 34.3887764
KING                 14530.9034 2075.84334 484.363446 39.8106942
TURNER               14600.9034 2085.84334 486.696779  40.002475
ADAMS                12517.9034 1788.27191 417.263446 34.2956257
JAMES                14514.9034 2073.55762 483.830112 39.7668585
FORD                 14514.9034 2073.55762 483.830112 39.7668585
MILLER               14463.9034 2066.27191 482.130112 39.6271325

6 显示昨天，今天和明天

//方法1

SQL> select sysdate-1 昨天, sysdate 今天, sysdate+1 明天
  2  from dual;

昨天           今天           明天
-------------- -------------- --------------
28-8月 -21     29-8月 -21     30-8月 -21
//方法2 to_char改变显示格式

SQL> select to_char(sysdate-1, 'yyyy-mm-dd') 昨天,
			to_char(sysdate, 'yyyy-mm-dd') 今天,
			to_char(sysdate+1, 'yyyy-mm-dd') 明天
2    from dual;

昨天                 今天                 明天
-------------------- -------------------- --------------------
2021-08-28           2021-08-29           2021-08-30

7.显示日期和时间

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 日期时间 from dual;

日期时间
--------------------------------------
2021-08-29 21:55:36