SQL/MySQL/Procedure Function/DATE

Материал из SQL эксперт
Перейти к: навигация, поиск

Date calculation in a procedure

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()

   -> BEGIN
   ->
   ->   DECLARE TwentyYearsAgoToday DATE;
   ->   DECLARE mystring VARCHAR(250);
   ->
   ->   SET TwentyYearsAgoToday=DATE_SUB(curdate(), interval 20 year);
   ->
   ->   SET mystring=CONCAT("It was ",TwentyYearsAgoToday," ...");
   ->
   ->   SELECT mystring;
   ->
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> mysql> call myProc(); +-----------------------+ | mystring | +-----------------------+ | It was 1987-07-23 ... | +-----------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.02 sec) mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql>

       </source>
   
  


DATE DEFAULT "1999-12-31"

   <source lang="sql">

mysql> mysql> mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()

   -> BEGIN
   ->      DECLARE l_date      DATE DEFAULT "1999-12-31";
   ->
   ->     select l_date;
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> mysql> call myProc(); +------------+ | l_date | +------------+ | 1999-12-31 | +------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql>

       </source>
   
  


DATE type variable

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()

   -> BEGIN
   ->     DECLARE my_dob         DATE
   ->             DEFAULT "1960-06-21";         /* My Birthday */
   ->
   ->     select my_dob;
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> mysql> call myProc(); +------------+ | my_dob | +------------+ | 1960-06-21 | +------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.02 sec) mysql> mysql>

       </source>