i'm trying create procedure prevent insertion of incorrect date. table accepts integer 8 digits long april 28 2015 inserted 4282015.
my logic here create temp variables store month, date, , year , assign them values taking sub strings original 8 digit value. convert them strings , concatenate (i not sure if there's way concatenate int, if there better) convert , int inserted. have tried far.
update: "sample" table example, running on different table in poorly set database (my job run analysis , fix little). way have set up, date integer.
create table sample ( id int not null, date int not null ); create procedure insertdate ( date int(8)) begin declare month int; declare day int; declare year int; set month = substring(new.date, 1, 2); set day = substring(new.date, 3, 2); set year = substring(new.date, 5, 4); if ( month in(1, 12) , day in(1, 31) , year in(2012, 2013) ) declare temp int; #cast variables varchars concatenate #convert int inserted set temp = cast( (cast(month varchar(2)) + cast(day varchar(2)) + cast(year varchar(4))) int ); insert sample (id, date) values (1 ,temp); end if; end; if wants take , give me pointers or explain stuff, appreciated!
if sensible thing, create column date or datetime type. since have both mysql , sql-server tags don't know dbms using, both of them support date types.
don't make more complicated need to.
