SQL/MySQL/Procedure Function/DATE
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>