Cours en ligne de courte durée 22 Avril 2014 – Gestion des dates en SQL et RPG Volubis.fr Conseil et formation sur OS/400, I5/OS puis IBM i depuis 1994 ! Dans nos locaux, vos locaux ou par Internet Christian Massé - [email protected] Gestion des dates en SQL et RPG Comparatifs des ordres de manipulation de dates On ne peut utiliser l'arithmétique temporelle qu'avec des dates, des heures, des horodatages Les dates Type DATE sous SQL, type L avec SDD comprises entre 1er Janvier de l'AN 1 et 31 Décembre 9999 le format n'est qu'un critère d'affichage et est un paramètre SQL (strsql), ODBC, JDBC, etc.. => Toujours *ISO par défaut ! *ISO → *EUR → *DMY → 'yyyy-mm-dd' 'dd.mm.yyyy' 'dd/mm/yy' (attention entre 1940 et 2039) Gestion des dates en SQL et RPG Comparatifs des ordres de manipulation de dates Constantes : au format par défaut. • en SQL (fonction du paramétrage, F13 sous STRSQL) 'yyyy-mm-dd', fonctionne toujours par exemple select * from PAUSECAFE where datepause = '2006-01-17' • en RPG (fonction de DATFMT en spécif H, sinon *ISO) encadrer la constante de D' D wdate S ' , par exemple INZ(D'2006-01-17') Gestion des dates en SQL et RPG Les heures Type TIME sous SQL, type T avec SDD comprises entre 0 heures (00:00:00) et Minuit (24:00:00) le format le plus simple est *HMS (hh:mm:ss) le format par défaut est *ISO (hh.mm.ss) Constantes en SQL, au format par défaut entre quotes 'hh.mm.ss' en RPG (TIMFMT en spécif H), encadré de T' ' Horodatage Type TIMESTAMP sous SQL, type Z sous SDD Contient Date + Heure avec une précision de 6 décimales après la seconde format *ISO -> 'yyyy-mm-dd-hh.mm.ss.cccccc' Constantes en SQL, au format par défaut entre quotes en RPG , encadré de Z' ' Gestion des dates en SQL et RPG Pour les horodatages : Sous SQL on peut déclarer AS ROW CHANGE TIMESTAMP create table clients (nocli int as identity, nom char(30), quand not null FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP) cette zone est alors renseignée à chaque insertion et mise à jour ROW CHANGE TIMESTAMP for clients, retourne le timestamp de dernière modification de chaque ligne select * from clients where DATE(row change timestamp for clients) = current date les lignes modifiées aujourd'hui Gestion des dates en SQL et RPG Les calculs les calculs peuvent se faire sous la forme date + durée = date date - durée = date date - date = durée heure + durée = heure etc .. les durées peuvent être exprimées de manière explicite avec : une valeur numérique entière n et : Un mot réservé pour SQL Une fonction intégrée pour RPG Gestion des dates en SQL et RPG Les calculs Pour SQL n YEARS n HOURS n MONTHS n DAYS n MINUTES n SECONDS Exemple : select * from commandes where datliv = datcde + 2 MONTHS + 10 DAYS Pour RPG %YEARS(n) %MONTHS(n) %DAYS(n) %HOURS(n) %MINUTES(n) %SECONDS(n) Exemple : /free datliv = datcde + %MONTHS(2) + %DAYS(10); /end-free l Gestion des dates en SQL et RPG Les calculs en SQL les durées résultat (datcde - datliv) seront toujours exprimées sous la forme AAAAMMJJ, où : AAAA représente le nombre d'années MM le nombre de mois JJ le nombre de jours Ainsi, si SQL affiche 812, il faut comprendre 8 mois, 12 jours 40301 signifie 4 ans , 03 mois, 01 jour (attention SQL risque d'afficher 40.301) select * from commandes where (datliv – datcde) = 210 Gestion des dates en SQL et RPG Les calculs en RPG les durées résultat (datcde - datliv) sont exprimées en une seule unité, indiquée en argument de la fonction %DIFF Exemple : /free ecartenjours = %DIFF(datLIV : datCDE : *DAYS); /end-free En spécif C, vous utilisiez SUBDUR C DATCDE SUBDUR DATLIV ecart :*D Gestion des dates en SQL et RPG Fonctions liées aux dates (comparatif SQL // RPG) Gestion des dates en SQL et RPG Fonctions liées aux dates en SQL Gestion des dates en SQL et RPG Fonctions liées aux dates (comparatif SQL // RPG) Gestion des dates en SQL et RPG Fonctions liées aux dates (comparatif SQL // RPG) Gestion des dates en SQL et RPG Fonctions liées aux dates (comparatif SQL // RPG) Gestion des dates en SQL et RPG Fonctions liées aux dates (comparatif SQL // RPG) Enfin en cas de conversion d'une zone décimale contenant une date invalide (0 par ex.) SQL affiche ++++++++ En programmation il faut mettre une variable indicateur Select date( …) into wdate:wdatind Wdatind( 5 integer) contient alors -2 En RPG testez avant par : TEST(de) datchar; if %error ; ..... endif; Gestion des dates en SQL et RPG Fonctions liées aux heures(comparatif SQL // RPG) Gestion des dates en SQL et RPG Fonctions liées aux hotodatages (comparatif SQL // RPG) Gestion des dates en SQL et RPG Fonctions liées aux horodatages (spécifiques SQL) Gestion des dates en SQL et RPG Quelques exemples RPG * calcul de la fin de mois * astuce : pour le 17 Janvier, se positionner au 17 Février et enlever 17 jours * Dfindemois PI D D Datein D CONST P Findemois D D Datein B PI D Wdate S D D EXPORT CONST D /free wdate = Datein + %months(1); wdate = wdate - %days( %subdt(wdate : *DAYS) ); return wdate; /end-free P Findemois E Gestion des dates en SQL et RPG Quelques exemples RPG * calcul du N° jour de semaine * astuce : on part d'un dimanche connu (6 Janvier 80) * on divise l'écart en jours, le reste EST le N° de jour dans la semaine * sauf dimanche (= 0) ou on retourne 7. * Djoursemaine PI 1 0 D Datein D CONST P joursemaine D D Datein B PI D Wdate S 1 D 0 EXPORT CONST D /free nbdejours = %DIFF(wdate : D'1980-01-06' : *DAYS) ; reste = %REM(nbdejours : 7); if reste < 1; return reste + 7; else; return reste; endif; /end-free P joursemaine E Gestion des dates en SQL et RPG Quelques exemples RPG * calcul du N° de semaine dsemaine PR d datein psemaine d d datein d janvier4 d Lundi B pi S S 2P 0 D const export 2P 0 D const D INZ(D'0001-01-04') D /free // calcul du 4 janvier de l'année traitée janvier4 += %years(%SUBDT(DateIn : *Y) -1 ); // calcul du Lundi précédent le 4 janvier Lundi = janvier4 - %DAYS(joursemaine(janvier4)-1) ; // si date recue < au lundi ==> premier lundi,année précédente if DateIn < Lundi; ANjanvier4 -= 1; Lundi = janvier4 - %DAYS(joursemaine(janvier4)+1); endif; // nombre de semaine entières return %DIV( %DIFF(DateIn:Lundi:*DAYS) : 7) +1; /end-free Psemaine E Gestion des dates en SQL et RPG Quelques exemples RPG * même chose en utilisant SQL * calcul du N° de semaine en SQL dsemaineSQL PR 2P 0 d datein D const psemaineSQL d d datein d numsemaine /free B pi s export 2P 0 D const 2P 0 EXEC SQL values WEEK_ISO(:datein) into :num_semaine; return numsemaine; /end-free PsemaineSQL E Gestion des dates en SQL et RPG Quelques exemples en SQL Les commandes passées cette année Select * from commandes where YEAR(datcmd) = YEAR(current date) Les commandes passées ce trimestre Select * from commandes where YEAR(datcmd) = YEAR(current date) AND QUARTER(datcmd) = QUARTER(current date) les commandes de l'année écoulée Select * from commandes where datcmd between current date - 1 year + 1 day AND current date Gestion des dates en SQL et RPG Dates et DSPF Les mots-clé DATFMT [éventuellement DATSEP] sont admis sur les états et les écrans, depuis la V4R2. A LA SAISIE (DSPF) : - les séparateurs ne sont PAS obligatoires ! - les zéros [à gauche] non plus pour le 1er Avril 2014 on admet avec DATFMT(*DMY) : 01/04/14 010414 10414 1/4/14 on admet aussi DATFMT(*JOB) qui affiche (et accepte) une date au format du job en cours(*DMY en France), mais transmet TOUJOURS en *ISO Gestion des dates en SQL et RPG Dates et DSPF Enfin, un nouveau mot-clé MAPVAL est implémenté afin d'établir des conversions entre valeur affichée et valeur transmise au pgm. MAPVAL( (valeur-pgm1 valeur-écran1) (valeur-pgm2 valeur-écran2) ...) jusqu'à 100 correspondances avec comme valeurs particulières : *BLANK on affiche que des blancs *CUR valeur en cours (date en cours ou heure en cours) exemple : MAPVAL(('0001-01-01' *BLANK)) si le pgm contient 1er janv. de l'an 1 j'affiche des blancs ET si l'utilisateur entre des blancs j’envoie 1er janv. de l'an 1
© Copyright 2024 ExpyDoc