CODEWITHSUNDEEP
mysqlsql
Scott 混合理论
Scott 混合理论

Reputation: 2332

MYSQL how to declare a datetime variable?

My code: 

DECLARE report_date DATETIME;
set report_date='2013-01-17 00:00:00';

SELECT  *
  FROM `NMPP`.`capacitypersecond` 
  WHERE `StreamDT` >=report_date and `StreamDT` < '2013-01-18 00:00:00'  ;

SELECT  *
  FROM `NMPP`.`capacityperHr` 
  WHERE `StreamDT` >=report_date and `StreamDT` < '2013-01-18 00:00:00'  ;

SELECT  *
  FROM `NMPP`.`capacityperDay` 
  WHERE `TJLDate` >=report_date and `TJLDate` < '2013-01-18 00:00:00'  ;

-

DECLARE report_date DATETIME;
/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE report_date DATETIME' at line 1 */
/* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 5 queries: 0.000 sec. */

Upvotes: 18

Views: 99670

Answers (3)

Pazhani Samy
Pazhani Samy

Reputation: 21

All the DECLARE should be at the start of the trigger,stored procedure.

This is not a C++ like language where you can mix declarations and statements, but more like C, where all declarations must be done before all statements.

Upvotes: 1

spacepille
spacepille

Reputation: 4131

or with cast:

set @report_date = cast('2013-01-17 00:00:00' as datetime);

Upvotes: 20

palindrom
palindrom

Reputation: 19111

get rid of declare:

set @report_date = '2013-01-17 00:00:00';

SELECT  *
  FROM `NMPP`.`capacitypersecond` 
  WHERE `StreamDT` >= @report_date and `StreamDT` < '2013-01-18 00:00:00'  ;

Upvotes: 10

Related Questions