1- Confronto date, YEAR(), MONTH(), DAY(), CURDATE(), NOW(), CURTIME(), DATE_FORMAT(), GET_FORMAT(), DAYOFWEEK()
1. SELECT nome,cognome,data_nascita FROM clienti where data_nascita > '1949-10-01';
2. SELECT nome,cognome,data_nascita FROM clienti where YEAR(data_nascita) > '1949';
3. SELECT count(*) FROM clienti where MONTH(data_nascita) = 12;
4. SELECT count(*) FROM clienti where (DAY(data_nascita) = 2) and (MONTH(data_nascita) = 3);
5. SELECT CURDATE() as data_attuale;
6. SELECT NOW() as data_ora_attuale;
7. SELECT CURTIME() as ora_attuale;
8. SELECT nome, cognome, data_nascita, ((YEAR(CURDATE())-YEAR(data_nascita)) - (RIGHT(CURDATE(), 5)<RIGHT(data_nascita, 5))) as età FROM clienti;
9. SELECT DATE_FORMAT(data_nascita,'%d/%m/%Y') as data_nascita FROM clienti;
10. UPDATE ordini SET data_ora = concat('2011-01-12 ', TIME(data_ora)) WHERE id = 2
11. SELECT nome, cognome, DATE_FORMAT(data_nascita,'%W %e %b %Y') as data_nascita FROM clienti;
12. SELECT nome, cognome, DATE_FORMAT(data_nascita,GET_FORMAT(DATE,'EUR')) as data_nascita FROM clienti;
13.
SELECT DAYNAME(CURDATE());( impostare lingua )14.
SELECT MONTHNAME(CURDATE());( impostare lingua )2- DATEDIFF, TIMESTAMPDIFF, DATE_ADD, DATE_SUB, INTERVAL , + , -
1. SELECT DATEDIFF('2011-06-13',CURDATE()) AS giorni_di_scuola_rimasti 2. SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') AS diff_in_mesi;3. SELECT TIMESTAMPDIFF(YEAR,'2003-05-01',CURDATE()) AS anni_trascorsi;
4. SELECT TIMESTAMPDIFF(HOUR,'2011-04-16 10:10:00',NOW()) AS ore_trascorse;
1. SELECT (TIME_TO_SEC('8:30')- TIME_TO_SEC('15:30'))/60) 5. SELECT DATE_ADD('2008-01-02', INTERVAL 3 DAY) ;
6. SELECT DATE_SUB('2008-01-02', INTERVAL 3 DAY) ; 7. SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND ; 8. SELECT '2008-12-31 23:59:59' - INTERVAL 1 SECOND ; 9. SELECT '2008-12-31 23:59:59' + INTERVAL '1 10' DAY_HOUR ; 10. SELECT NOW() + INTERVAL '1 2:3' DAY_MINUTE ;
11. SELECT * FROM clienti where (RIGHT(data_nascita,5)<RIGHT(CURDATE()+INTERVAL 7 DAY,5)) and (RIGHT(data_nascita,5)>=RIGHT(CURDATE(),5));
