select all sales from specific day ? mysql , convert datetime to date-CodePudding

how to convert the DateTime column to date?

I need something like the select * from sales where date = = "2022-09-18";

I need to get all sales from a specific day

I tried this

I tried also this but it gave me error this

this is a table named Sales

if there is anybody that can help me understand how do I get this thing to work please i really appreciate that! a lot .

code for generating the database if you want

DROP DATABASE IF EXISTS ice_cream_store;
CREATE DATABASE IF NOT EXISTS ice_cream_store;
USE ice_cream_store; 

CREATE TABLE `Tastes` (
    `tid` INTEGER NOT NULL AUTO_INCREMENT primary key,
    `name`  VARCHAR(20) NOT NULL,
    UNIQUE (name)
);
CREATE TABLE `Toppings` ( 
    `topid` INTEGER NOT NULL AUTO_INCREMENT primary key,
    `name`  VARCHAR(20) NOT NULL,
    UNIQUE (name)
);
CREATE TABLE `Receptacles` ( 
    `rid` INTEGER NOT NULL AUTO_INCREMENT primary key,
    `name`  VARCHAR(20) NOT NULL,
    `price` int NOT NULL,
    UNIQUE (name)
);

CREATE TABLE `Sales` ( 
    `sid` INTEGER NOT NULL AUTO_INCREMENT primary key,
    `rid` integer not null,
    foreign key (`rid`) references Receptacles(`rid`),
    `datetime` datetime not null, 
    `completed` bool not null,
    `paid` bool not null,
`total_price` INTEGER NOT NULL
);

CREATE TABLE `Tastes_Sales` ( 
    `sid` integer not null,
    foreign key (`sid`) references Sales(`sid`),
    `tid` integer not null,
    foreign key (`tid`) references Tastes(`tid`),
    PRIMARY KEY (`sid` , `tid`),
    `quantity` integer not null
);

CREATE TABLE `Toppings_Sales` ( 
    `sid` integer not null,
    foreign key (`sid`) references Sales(`sid`),
    `topid` integer not null,
    foreign key (`topid`) references Toppings(`topid`),
    PRIMARY KEY (`sid` , `topid`)
);

SELECT 
       DATE(`datatime`)
         AS  date_of_booking
FROM sales;


select * from tastes;
select * from Receptacles;
select * from Toppings;
select * from sales;
select * from Toppings_Sales;
select * from Tastes_Sales;

CodePudding user response：

Use the extract function

Example:

SELECT EXTRACT(MONTH FROM "2017-06-15 09:34:21");

outputs 6

From there you just do:

select * from sales where EXTRACT(DAY FROM date) = 18;

Now you just add AND operators to do the same for month and year

I think this is a simple enough solution. Hope this works for you.

CodePudding user response：

SELECT * from sales WHERE DATE(datetime) = '2022-09-15'; this is working for me @RiggsFolly thank you very much!!