Date string-to-date procedure

Question

Date string-to-date procedure

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I'm creating a procedure, which receives a string '2015-09-11', verifies that it is indeed a Monday, if not, it picks up that date and plays Monday of the week itself. But my concept with sql is pretty weak, my base is this way.

    DROP PROCEDURE IF EXISTS SP_DATA_PARA_SEGUNDA;
DELIMITER //
CREATE PROCEDURE SP_DATA_PARA_SEGUNDA (IN SPA_DATA_CONVERTER DATE,INOUT SPA_DATA_CONVERTIDA INT)
    BEGIN            
         SELECT DATE_FORMAT(SPA_DATA_CONVERTER,'%w');
    END//
DELIMITER ;

Now it works, but now I want to return that dateformat in my SPA_DATA_CONVERTIDA argument, how do I?

sql procedure

asked by anonymous 11.09.2015 / 15:39

2 answers

0

I assume that the first day of the week is Sunday, meaning that if you pass as a parameter a date whose corresponding day of the week is Sunday, the stored procedure will return the next day (Monday).

The function DAYOFWEEK returns the week to which the date passed as a parameter corresponds. In your example the day of the week corresponding to the date '2015-09-11' is 6 (1 = Sunday, 2 = Monday, ..., 7 = Saturday)

Knowing this, and according to this function, the day corresponding to Monday is 2, we just have to add (subtract) the required number of days to get the desired date.

I did so, there are certainly other alternatives:

DROP PROCEDURE IF EXISTS SP_DATA_PARA_SEGUNDA;
DELIMITER //
CREATE PROCEDURE SP_DATA_PARA_SEGUNDA (IN SPA_DATA_CONVERTER DATE,
                                       OUT SPA_DATA_CONVERTIDA DATE)
BEGIN            
     SELECT ADDDATE(SPA_DATA_CONVERTER, INTERVAL 2 - DAYOFWEEK(SPA_DATA_CONVERTER) DAY) 
     INTO SPA_DATA_CONVERTIDA
END//
DELIMITER ;

Here's a small example . If you have any questions leave a comment.

13.09.2015 / 12:19

Is it possible to set the font size dynamically? Smart Screen blocks my application

score 0 · Accepted Answer

Good Morning Friend ...

Converting date to Day of Week can use the following:

SELECT DATENAME ( weekday , '20150911' )

Will return for example sixth! based on the day you can set up your function to add days or remove .. in case add 3 days to the Next Second ... Hope that helps!

To return in MySql a look at likns: link
link