How to check if datetime equal tomorrow in MySQL - mysql

I want to retrieve mysql table data if the data created_at Datetime column equal to tomorrow date, for example:
SELECT * FROM sales_order where created_at = tomorrow_date;

SELECT * FROM sales_order where created_at = CURDATE() + 1;

You can use the following, using DAEDIFF AND DATE_ADD:
SELECT * FROM sales_order WHERE DATEDIFF(created_at, DATE_ADD(CURDATE(), INTERVAL 1 DAY)) = 0;
or a simpler solution only using DATEDIFF:
SELECT * FROM sales_order WHERE DATEDIFF(created_at, CURDATE()) = 1
DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation. - from MySQL docs.

Related

Want to display only those dates whose month is '10' using Mysql

I Have one column in the table called "date". In this column there are some dates stored from different months. I want to display only those dates whose month is '10'. How can we write query to print the desired output using substring() function of mysql ? Or else any other solution for this ?
O/p : first 8 records from this column.
MySQL has a lot of options for working with dates. In this case, combining the MONTH() function with STR_TO_DATE() would be easiest; MONTH() takes a date as input and returns the month number, starting at 1 for January, while STR_TO_DATE() will format your (non-standard) date string into a date MySQL understands.
Your query would then become:
SELECT * FROM your_table WHERE MONTH(STR_TO_DATE(`date`, '%d-%m-%Y')) = 10;
select * from your_table where month(date) = 10;
Use DATE_FORMAT then DATE_FORMAT(date, '%m') to be able to get the value of month alone
SELECT * FROM tbl WHERE DATE_FORMAT(date, '%m') = '10'
SELECT `date` FROM table_name
WHERE MONTH(`date`) = '10'
ORDER BY `date` -- without this, the a random 8 would be delivered
LIMIT 8
If date column is datetime or Time stamp then
SELECT `date` FROM `tablename` WHERE month(`date`) =10

How to generate all dates between two dates

How can I retrieve all dates between '2015-10-02' to '2015-11-02' in SQLite? (String type)
Result will be like:
'2015-10-03'
'2015-10-04'
'2015-10-05'
...
'2015-11-01'
This is not a question about SELECT * FROM myTable where myDate <= '2015-01-01' AND myDate >= '2015-01-31'. This is not about selecting all existing records which have a field between two days. I just want to retrieve all possible date values between two dates. I want to use them to query the count of record by days.
Date Count
'2015-01-01' 19
'2015-01-02' 10
'2015-01-03' 0
...
This is not possible without a recursive common table expression, which was introduced in SQLite 3.8.3:
WITH RECURSIVE dates(date) AS (
VALUES('2015-10-03')
UNION ALL
SELECT date(date, '+1 day')
FROM dates
WHERE date < '2015-11-01'
)
SELECT date FROM dates;
If your date column is already a date type with the format YYYY-MM-DD then you can use the following query:
SELECT date_field AS Date, COUNT(date_field)
FROM table
WHERE (date_field BETWEEN '2015-10-02' AND '2015-11-02')
GROUP BY date_field
If the date column is actually a string, then you can use the strftime() function:
SELECT date_field AS Date, COUNT(date_field)
FROM table
WHERE strftime('%Y-%m-%d', date_field) BETWEEN '2015-10-02' AND '2015-11-02'
GROUP BY date_field

MySQL select dates between with date functions and min max

Why does this return '2015-01-19'?:
SELECT date
FROM hours
HAVING date BETWEEN DATE_SUB(max(date), INTERVAL 2 DAY) AND DATE_ADD(max(date), INTERVAL 1 DAY)
when this returns '2015-01-20' and '2015-01-19'?:
SELECT date
FROM hours
HAVING date BETWEEN '2015-01-18' AND '2015-01-21'
max date is '2015-01-20' and there are records on both '2015-01-20' and '2015-01-19'. date is a date field.
I think you'll find that date_sub returns a full time stamp e.g. '2008-11-11 13:23:44.657' see http://www.w3schools.com/sql/func_date_sub.asp for more details.
You'll want to use the extract method or date_format ( DATE_FORMAT(date_time_var, '%Y-%m-%d') ) to get just the day, month and year as used in your between statement.
Your first example contains aggregate functions. HAVING typically requires a GROUP BY, because it's a means to filter groups. So putting GROUP BY date before HAVING date will get you both the 19th and 20th (for your first SQL statement).
That's HAVING behaviour.
HAVING will group results in your first query because there is a grouping function MAX in HAVING clause. All returned rows will be grouped in one row-group because there is no GROUP BY instruction. Therefore, you only get one result. date value of this group can be any of the grouped rows.
HAVING will do no group in second query because there is no grouping function in HAVING clause, nor GROUP BY instruction.
Adding a GROUP BY date as suggested will group rows that have the same date; hence MAX will only return that date value instead of MAX of all dates. So, you will retrieve all rows because MAX(date)==date in all grouped rows.
SELECT `date`, GROUP_CONCAT(`date`), (SELECT MAX(`date`) FROM `hours`) AS `md`
FROM `hours`
GROUP BY `date`
HAVING `date`
BETWEEN DATE_SUB(md, INTERVAL 2 DAY) AND DATE_SUB(md, INTERVAL 1 DAY);
You might also use
SELECT `hours`.`date`, `md`.`d`
FROM `hours`, (SELECT MAX(`date`) AS `d` FROM `hours`) AS `md`
WHERE `hours`.`date`
BETWEEN DATE_SUB(`md`.`d`, INTERVAL 2 DAY) AND DATE_ADD(`md`.`d`, INTERVAL 1 DAY);
Not sure which one is the fastest...

MYSQL get last date from table that has not yet passed [closed]

In my database in table_a every row has a date_created like "2011-04-17"
Now some of these dates are in the past, but my question is how can I retrieve the latest date that has not yet passed?
Try this one
SELECT * FROM table_a WHERE CURDATE() <= date_created
CURDATE()
Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD
format, depending on whether the function is used in a string or
numeric context.
Can you try this,
SELECT * FROM table_a WHERE date_created >= CURDATE()
IF date_created is a date datatype then you can use
SELECT *
FROM table_a
WHERE date_created >= NOW()
LIMIT 1

how to test date and datetime with mysql

My table is using a datetime (YYYY-MM-DD HH:MM:SS) and i need to display today's entries.
my code is only :
SELECT *
FROM table
WHERE date = '$date'
ORDER BY score DESC
with
$date = date("Y-m-d");
well, as expected it doesnt work :| you guys have a solution here ?
Following from Pascal Martin, you could extract the date part from the date+time field:
SELECT * FROM table WHERE DATE(date) = '2009-12-19'
Source: MySQL - Date and Time Functions
Be aware however, that this query will not use an index on your date+time field, if you will be having one. (Stack Overflow: How does one create an index on the date part of DATETIME field in MySql)
Your date is "2009-12-19" (or something like that, depending on the day), which is interpreted as "2009-12-19 00:00:00".
In your database, you probably don't have any date that's exactly equal to that one, by the second : your dates are like "2009-12-19 12:15:32".
A solution is to compare like this :
select *
from table
where date >= '2009-12-19'
and date < '2009-12-20'
Which will be interpreted as :
select *
from table
where date >= '2009-12-19 00:00:00'
and date < '2009-12-20 00:00:00'
And, if you don't want to do the math to get the date of the following date, you can use the adddate function :
select *
from table
where date >= '2009-12-19'
and date < adddate('2009-12-19', interval 1 day)
So, in your case, something like this should do the trick :
select *
from table
where date >= '$date'
and date < adddate('$date', interval 1 day)
order by score desc
You probably want to format the data when you select it:
SELECT *, DATE_FORMAT(date, '%Y-%m-%d') AS dateformat FROM table
WHERE dateformat = '$date' ORDER BY score DESC
You are comparing datetime and date expression, that is why its not working. Use Date() method to return the date part from datetime and then do the comparison. WHERE DATE(date) = '$date' should do. You might have to use aliases to handle this name collision.

Resources