Get record from mysql from past 7 days

Question

0.00/5 (No votes)

See more:

hi guys,

i have used mysql in my web App. i am trying to get the past 7days record from today.. but i unable to get the record.can you someone please help me on the query

Thu Jan 06 2022 10:04:19 GMT+0530 (India Standard ...
10
Xerox
0
Thu Jan 06 2022 10:04:47 GMT+0530 (India Standard ...
2
Xerox
recharge
0
Thu Jan 06 2022 10:27:06 GMT+0530 (India Standard ...
55
Xerox
UNION BANK ACCOUNT OPEN
0
Thu Jan 06 2022 10:35:07 GMT+0530 (India Standard ...
3
Xerox
0
Thu Jan 06 2022 10:57:43 GMT+0530 (India Standard ...
52
Xerox
ONLION ACCOUNT OPEN
0
Thu Jan 06 2022 10:57:55 GMT+0530 (India Standard ...
4
Xerox
0
Thu Jan 06 2022 11:16:15 GMT+0530 (India Standard ...
12
Xerox
0
Thu Jan 06 2022 11:16:31 GMT+0530 (India Standard ...
10
Xerox
0
Thu Jan 06 2022 11:27:49 GMT+0530 (India Standard ...
6
Xerox
0
Thu Jan 06 2022 11:27:57 GMT+0530 (India Standard ...
10
Xerox
0
Thu Jan 06 2022 11:28:31 GMT+0530 (India Standard ...
100
Others
MONEY TRANSFER
0
Thu Jan 06 2022 11:48:03 GMT+0530 (India Standard ...
10
Others
MONEY TRANSFER
0
Thu Jan 06 2022 11:48:28 GMT+0530 (India Standard ...
33
Lamination
0
Thu Jan 06 2022 12:00:46 GMT+0530 (India Standard ...
4
Others
Revenue Stamp
0
Thu Jan 06 2022 12:03:41 GMT+0530 (India Standard ...
6
Others
Xerox and Revenue Stamp
0

What I have tried:

below one is my table record date column will be like that format only

Posted 15-Jan-22 21:40pm

Muthu vj

Updated 15-Jan-22 22:14pm

Add a Solution

Comments

Maciej Los 16-Jan-22 3:44am

And what have you tried till now? Where are you stuck?

Muthu vj 16-Jan-22 3:50am

select * from ProfitSubmitDetails
where date between Date(now(),INTERVAL 1 WEEK) and now();

Date(string) SaleCatagory(string) Profit(string) Expense(string) Comment(string)

this is the Column name with Type

Richard MacCutchan 16-Jan-22 4:20am

Why are you storing Date, Profit and Expense as strings, instead of their actual types: DateTime, Decimal etc.?

Muthu vj 16-Jan-22 3:55am

SELECT * FROM ProfitSubmitDetails WHERE STR_TO_DATE(Date, '%d/%m/%Y') BETWEEN '2022-01-11' AND '2022-01-16'

i have tried this also

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2022-01-15T22:14:00

At a guess, you database design is wrong: you are storing dates as strings instead of DATETIME values.

When you compare strings, the result of the comparison is based on a comparison of the first non-matching characters in the two strings - any other characters are ignored.
So date based data in particular does not compare well: even if the same day is found ("Thu" in your examples) "Jan" will come after "Feb", but before "Mar".

Check your DB, and change string based storage of any and all numeric values to the appropriate data types: INT, FLOAT, DECIMAL, DATETIME, and change your presentation software to reflect that.