adding value to IN variable

Question

I need to assign 21600000 to fromDate IF the value originally sent in is zero

Code that I have now is

CREATE PROCEDURE reportFreeCooling(
  IN fromDate VARCHAR (50),
  IN toDate   VARCHAR (50),
  IN timeZone VARCHAR (50)
)
BEGIN
  DECLARE startDate VARCHAR (50);
  DECLARE endDate   VARCHAR (50);
  DECLARE startDateOriginal VARCHAR (50);
  DECLARE mylogID   INT;
  DECLARE myItemId varchar (50);
  DECLARE myItemId2 varchar (50);
  DECLARE xHours varchar (50);
  DECLARE endHoursNull varchar(50);
  DECLARE endHoursNotNull varchar (50);

and of course the rest of the stored procedure. The rest is correct I just need to know how to change the fromDate if zero is what is sent in.

use a local variable to store the value you need, 21600000 if 0 given... PS: why using varchars? — Sebas, Dec 30 '13 at 00:32
using varchars due to the way the application processes - if i use timestamps or dates then it gives an odd result - believe me I have tried!...I have tried putting an IF fromDate = 0 then fromDate = 21600000 but that blew up on me! I am not sure what I need to set — Portlight, Dec 30 '13 at 00:40
I tried IF fromDate = 0 THEN SET @fromDate = '21600000'; but could not add an IF != 0 then fromDate so what am I missing — Portlight, Dec 30 '13 at 00:43
basically this is what i need to code to do..... IF fromDate = 0 THEN SET #fromDate = '21600000'; IF fromDate > 0 THEN SET #fromDate = fromDate; SET startDate = FROM_UNIXTIME(#fromDate/1000); ......but of course this is not working! #date = @fromDate in above code as stackflow thinks everytime I use the 'at' symbol I am addressing a user. — Portlight, Dec 30 '13 at 00:47

score 2 · Accepted Answer · edited May 23 '17 at 12:28

2

I haven't tested any of these but they may point you in the right direction.

OPTION 1: compare fromDate using a string comparison ...

IF fromDate = "0" THEN 
   SET @fromDate = '21600000'; 
ELSEIF fromDate > 0 THEN 
   ...
ELSE  
   ...
END IF;

OPTION 2: convert fromDate from varchar to int

DECLARE fromDateInt INT;

SELECT CONVERT(fromDate as SIGNED) INTO fromDateInt;
IF fromDate = 0 THEN 
   SET @fromDate = '21600000'; 
...

Cast from VARCHAR to INT - MySQL

Also, you may want to check/handle: 1. Could fromDate be a float?
2. Could fromDate be NULL?

edited May 23 '17 at 12:28

Community

1
1

answered Dec 30 '13 at 01:01

user2942090

46
5

I tried IF fromDate = "0" THEN SET #fromDate = '21600000'; ELSE SET #fromDate = fromDate END IF; and it gave me an error that says there is a ; expected before the END I also tried IF fromDate = "0" THEN SET #fromDate = '21600000'; ELSEIF fromDate > 0 THEN SET #fromDate = fromDate END IF; and it gave me the same error.....no I do not want to convert it to an int and no fromDate could not be null or a float - – Portlight Dec 30 '13 at 01:10
I edited to include a ";" after "END IF". Is that your issue? – user2942090 Dec 30 '13 at 01:16
no I tried adding a ; to END IF - so END IF; gives me an error .... it is saying it expected a ; before the END but I know that is wrong – Portlight Dec 30 '13 at 02:20

score 0 · Answer 2 · answered Dec 30 '13 at 04:14

0

This piece of code actually required a ; at the end of the set statement and the end of the if statment.

IF firstRowDate > startDateOriginal THEN SET startDateOriginal = firstRowDate; END IF;

answered Dec 30 '13 at 04:14

Portlight

95
9

adding value to IN variable

2 Answers2