CODEWITHSUNDEEP
mysqlsql
Sumon
Sumon

Reputation: 299

in mysql how can i set a variable with last date and time

I am trying to create a two variable which will hold yesterday date and time without manually enter date, suppose today date is 26/01/2017 and my variable should hold like

declare p_run_from_date datetime;
declare p_run_to_date datetime;

set p_run_from_date = '2016-01-25 00:00:00';
set p_run_to_date = 2016-01-25 23:59:59';

Code could be like below but do not know how to set time

 set p_run_from_date = ( CURDATE() - INTERVAL 1 DAY )

Upvotes: 1

Views: 1606

Answers (3)

Raymond Nijland
Raymond Nijland

Reputation: 11602

You can use CURDATE() - INTERVAL 1 SECOND 

SELECT CURDATE() - INTERVAL 1 SECOND

Result

CURDATE() - INTERVAL 1 SECOND  
-------------------------------
2017-01-25 23:59:59

So the answer should be

set p_run_to_date = (CURDATE() - INTERVAL 1 SECOND)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

First, you should change your logic. Instead of:

where date between p_run_from_date and p_run_to_date

or

where date >= p_run_from_date and date <= p_run_to_date

Use:

where date >= p_run_from_date and date < p_run_to_date

That inequality makes things much simpler. And, you don't have to worry about fractions of seconds.

For this:

set p_run_from_date = date_sub(CURDATE(), interval 1 day);
set p_run_to_date = CURDATE();

I should point out that the answer to your particular question is:

set p_run_from_date = date_sub(CURDATE(), interval 1 day);
set p_run_to_date = date_sub(CURDATE(), interval 1 second);

Upvotes: 1

Hugo Rafael Azevedo
Hugo Rafael Azevedo

Reputation: 61

Try this:

DATE_ADD(now(), interval -1 day)

To your latest question, try this:

TIMESTAMP(DATE(DATE_ADD(now(), interval -1 day)))

Upvotes: 0

Related Questions