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
– Sebas, Commented Dec 30, 2013 at 0: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
– Portlight, Commented Dec 30, 2013 at 0: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
– Portlight, Commented Dec 30, 2013 at 0: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
– Portlight, Commented Dec 30, 2013 at 0:47

Community · Accepted Answer · 2017-05-23 12:28:41Z

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, 2017 at 12:28

CommunityBot

11 silver badge

answered Dec 30, 2013 at 1:01

user2942090

465 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Portlight Over a year ago

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 -

user2942090 Over a year ago

I edited to include a ";" after "END IF". Is that your issue?

Portlight Over a year ago

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 · Accepted Answer · 2013-12-30 04:14:57Z

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, 2013 at 4:14

Portlight

951 silver badge9 bronze badges

Collectives™ on Stack Overflow

adding value to IN variable

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related