Cours SQL ORACLE – 06 : Date et SYSDATE
Dans ce nouveau cours, nous allons voir comment on gère les dates dans Oracle. Comme mentionné dans mon premier cours, le format date et l’un des formats les plus utilisés.
Donc pour commencer ce cours, nous allons voir comment sélectionner la date du jour (date système) grâce au mot clé SYSDATE :
- SYSDATE
SELECT SYSDATE FROM DUAL;
SYSDATE
-------------------------
09/02/16
Nous pouvons formater le résultat en utilisant la fonction TO_CHAR(<DATE>, <Format à afficher>)
- TO_CHAR
SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'DD/MM/YYYYHH24:MI:SS')
---------------------------------------
09/02/2016 22:48:43
Nous pouvons choisir la partie à afficher dans la date en modifiant le format à afficher de la fonction TO_CHAR:
SELECT
TO_CHAR(SYSDATE, 'DD') JOUR,
TO_CHAR(SYSDATE, 'MM') MOIS,
TO_CHAR(SYSDATE, 'YYYY') ANNEE,
TO_CHAR(SYSDATE, 'HH24') HEURE,
TO_CHAR(SYSDATE, 'MI') MINUTE,
TO_CHAR(SYSDATE, 'SS') SECONDE
FROM DUAL;
JOUR MOIS ANNEE HEURE MINUTE SECONDE
---- ---- ----- ----- ------ -------
09 02 2016 22 48 43
Le format à afficher peut être comme suit :
- TO_CHAR(SYSDATE, ‘D’): retourne le numéro du jour dans la semaine.
- TO_CHAR(SYSDATE, ‘DD’) : retourne le numéro du jour dans le mois.
- TO_CHAR(SYSDATE, ‘DY’) : retourne le nom abrégé du jour dans la semaine.
- TO_CHAR(SYSDATE, ‘DAY’) : retourne le nom complet du jour dans la semaine.
- TO_CHAR(SYSDATE, ‘DDD’) : retourne le numéro du jour dans l’année.
- TO_CHAR(SYSDATE, ‘MM’) : retourne le numéro du mois dans l’année.
- TO_CHAR(SYSDATE, ‘MON’) : retourne le nom abrégé du mois.
- TO_CHAR(SYSDATE, ‘MONTH’) : retourne le nom complet du mois.
- TO_CHAR(SYSDATE, ‘YY’) : retourne l’année sur deux digits.
- TO_CHAR(SYSDATE, ‘YYYY’) : retourne l’année sur quatre digits.
- TO_CHAR(SYSDATE, ‘HH’) : retourne l’heure au format 12h.
- TO_CHAR(SYSDATE, ‘HH24’) : retourne l’heure au format 24h.
- TO_CHAR(SYSDATE, ‘MI’) : retourne les minutes.
- TO_CHAR(SYSDATE, ‘SS’) : retourne les secondes.
Nous pouvons combiner plusieurs de ces formats en ajoutant des séparateurs (-/,. ;:) ou bien du texte à condition que le texte soit entre « " »
SELECT TO_CHAR(SYSDATE, '"Aujourd''hui, nous sommes le " : DAY, DD MONTH YYYY') FROM DUAL;
TO_CHAR(SYSDATE,'"AUJOURD''HUI,NOUSSOMMESLE":DAY,DDMONTHYYYY')
--------------------------------------------------------------
Aujourd'hui, nous sommes le : MARDI , 09 FÉVRIER 2016
Nous pouvons bien sûr utiliser la fonction TO_CHAR sur un champ de type date
SELECT 'L''employé ' || ENAME || ' a été embauché le ' || TO_CHAR(HIREDATE, 'DAY, DD MONTH YYYY') FROM EMP ORDER BY HIREDATE;
'L''EMPLOYÉ'||ENAME||'AÉTÉEMBAUCHÉLE'||TO_CHAR(HIREDATE,'DAY,DDMONTHYYYY')
--------------------------------------------------------------------------
L'employé SMITH a été embauché le MERCREDI, 17 DÉCEMBRE 1980
L'employé ALLEN a été embauché le VENDREDI, 20 FÉVRIER 1981
L'employé WARD a été embauché le DIMANCHE, 22 FÉVRIER 1981
L'employé JONES a été embauché le JEUDI , 02 AVRIL 1981
L'employé BLAKE a été embauché le VENDREDI, 01 MAI 1981
L'employé CLARK a été embauché le MARDI , 09 JUIN 1981
L'employé TURNER a été embauché le MARDI , 08 SEPTEMBRE 1981
L'employé MARTIN a été embauché le LUNDI , 28 SEPTEMBRE 1981
L'employé KING a été embauché le MARDI , 17 NOVEMBRE 1981
L'employé JAMES a été embauché le JEUDI , 03 DÉCEMBRE 1981
L'employé FORD a été embauché le JEUDI , 03 DÉCEMBRE 1981
L'employé MILLER a été embauché le SAMEDI , 23 JANVIER 1982
L'employé SCOTT a été embauché le DIMANCHE, 19 AVRIL 1987
L'employé ADAMS a été embauché le SAMEDI , 23 MAI 1987
Le résultat retourné par TO_CHAR est de type chaîne de caractère.
Nous pouvons aussi convertir une chaîne de caractère de format date en type DATE par la fonction TO_DATE(<Chaîne de caractère au format d’une date>, <Format de la date>) :
- TO_DATE
SELECT TO_DATE('01/01/2016', 'DD/MM/YYYY') FROM DUAL;
TO_DATE('01/01/2016','DD/MM/YYYY')
-------------------------
01/01/16
L’intérêt de l’utilisation de TO_DATE est la conversion d’une chaine de caractère en DATE pour pouvoir manipuler le résultat comme étant une DATE. Par exemple, nous voulons afficher les employés qui ont été embauché entre le 01/01/1981 et le 31/12/1982 :
SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE BETWEEN TO_DATE('01/01/1981', 'DD/MM/YYYY') AND TO_DATE('31/12/1982', 'DD/MM/YYYY') ORDER BY HIREDATE;
EMPNO ENAME HIREDATE
---------------------- ---------- -------------------------
7499 ALLEN 20/02/81
7521 WARD 22/02/81
7566 JONES 02/04/81
7698 BLAKE 01/05/81
7782 CLARK 09/06/81
7844 TURNER 08/09/81
7654 MARTIN 28/09/81
7839 KING 17/11/81
7900 JAMES 03/12/81
7902 FORD 03/12/81
7934 MILLER 23/01/82
Nous pouvons aussi manipuler les dates en faisant des soustractions ou des additions :
SELECT SYSDATE, SYSDATE + 1, SYSDATE + 366, SYSDATE - 365 FROM DUAL;
SYSDATE SYSDATE+1 SYSDATE+366 SYSDATE-365
------------ ------------ ------------ ------------
09/02/16 10/02/16 09/02/17 09/02/15
1 correspond à un jour.
Le résultat de TO_DATE est de type DATE.
Il existe plusieurs autres fonctions de date. Ci-dessous quelques exemples:
- LAST_DAY :
Retourne le dernier jour du mois d’une date
SELECT LAST_DAY(SYSDATE), LAST_DAY(TO_DATE('01/01/2016', 'DD/MM/YYYY')) FROM DUAL;
LAST_DAY(SYSDATE) LAST_DAY(TO_DATE('01/01/2016','DD/MM/YYYY'))
------------------------- -------------------------
29/02/16 31/01/16
Le résultat est de type DATE.
- MONTHS_BETWEEN
Retourne le nombre de mois entre deux dates :
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('09/02/2015', 'DD/MM/YYYY')) FROM DUAL;
MONTHS_BETWEEN(SYSDATE,TO_DATE('09/02/2015','DD/MM/YYYY'))
----------------------------------------------------------
12
Le résultat est de type NUMBER.
- EXTRACT
Retourne l’année, le mois, le jour, l’heure, la minute ou la seconde d’une date :
SELECT EXTRACT(YEAR FROM SYSDATE),
EXTRACT(MONTH FROM SYSDATE),
EXTRACT(DAY FROM SYSDATE)
FROM DUAL;
EXTRACT(YEARFROMSYSDATE) EXTRACT(MONTHFROMSYSDATE) EXTRACT(DAYFROMSYSDATE)
------------------------ ------------------------- -----------------------
2016 2 9
Le résultat est de type NUMBER.
Nous allons nous contenter de ces exemples. Nous allons voir d’autres fonctions liés aux dates dans d’autres chapitres.