Data string-to-date Procedure

Question

Data string-to-date Procedure

Asked 8 years, 9 months ago

Viewed 104 times

1

I am creating a Trial, which receives a date string '2015-09-11', checks if it is indeed a Monday, if not, it takes this date and play for Monday of the week itself. But my concept with sql is very weak, my basis 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 this dateformat na in my argument SPA_DATA_CONVERTIDA, as I do?

2 answers

0

Bom Dia Amigo...

Convert date to Weekday can use the following:

SELECT DATENAME ( weekday , '20150911' )

Will return for example Friday! based on the day you can assemble your function to add days or remove.. in case add 3 days to Next Monday... I hope it helps!

To return to Mysql from a look at likns: https://stackoverflow.com/questions/4362722/return-value-from-mysql-stored-procedure
https://stackoverflow.com/questions/26760414/mysql-stored-procedure-return-value

I see some functions, but I don’t know if there are restrictions, I can use both mysql and sql functions within the procedures?

– Michel Henriq

2015/09/11 at 14:10
WOULD THAT be? DROP PROCEDURE IF EXISTS SP_DATA_PARA_SEGUNDA;
DELIMITER //
CREATE PROCEDURE SP_DATA_PARA_SEGUNDA (IN SPA_DATA_CONVERTER DATE, OUT SPA_NOME_DIA VARCHAR(20)
 BEGIN 
 SPA_NOME_DIA = SELECT DATENAME(weekday,SPA_DATA_CONVERTER);
 END//
DELIMITER ;


– Michel Henriq

2015/09/11 at 14:15
The one I made would work on Sqlserver.. For mysql, From a Look at: [http://stackoverflow.com/questions/23235023/1305-function-datename-does-not-exist]

– PachecoDt

2015/09/11 at 14:18
I hope it helps you! (http://stackoverflow.com/questions/17975359/sql-group-by-datename)(http://www.suhailkaleem.com/mssql-datename-alternative-for-mysql/)

– PachecoDt

2015/09/11 at 14:25
I changed my question, now it’s working, but how do I return qd I give the call?

– Michel Henriq

2015/09/11 at 14:34
I changed the Answer.. With some Links. You probably need to use an OUT variable. or save to some table..

– PachecoDt

2015/09/11 at 14:47
I created a variable out, but how do I pass the value of the select into this var? I tried so, but it wasn’t.. SET SPA_DATA_CONVERTIDA = SELECT DATE_FORMAT(SPA_DATA_CONVERTER,'%w') as SPA_DATA_CONVERTIDA;

– Michel Henriq

2015/09/11 at 14:51
Let’s go continue this discussion in chat.

– PachecoDt

2015/09/11 at 17:22

Show 3 more comments

Browser other questions tagged sql procedure

You are not signed in. Login or sign up in order to post.

by bruno • **7,237** points · Answer 1 · 2015-09-13T10:19:38+00:00

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 day of the week to which the date passed as 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 as according to this function, the day corresponding to Monday is 2, we only have to add (subtract) the number of days necessary to obtain the desired date.

I did so, surely there are 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 ;

You got a little example. If you have any questions leave a comment.