SELECT database data With Date

Question

I Have a Table Name transactions

+-------+---------------------+--------+
| t_id  | date                | amount |
+-------+---------------------+--------+
|   10  | 2016-04-17 19:24:05 | 1800   |
|   12  | 2016-06-11 12:40:13 | 200    |
|   17  | 2016-04-13 14:42:04 | 100    |
|   19  | 2016-05-14 17:45:43 | 1      |
|   20  | 2016-08-15 19:45:54 | 999    |
|   21  | 2016-01-17 11:46:02 | 1500   |
|   41  | 2016-02-18 17:23:14 | 500    |
|   42  | 2016-07-19 13:26:14 | 500    |
|   43  | 2016-02-18 17:23:15 | 500    |
|   44  | 2016-02-18 17:23:16 | 500    |
|   45  | 2016-02-18 18:23:16 | 500    |
|   46  | 2016-02-18 17:23:16 | 500    |
|   47  | 2015-10-18 14:23:17 | 500    |
|   48  | 2015-11-18 17:23:17 | 500    |
|   49  | 2015-12-18 11:23:18 | 500    |
|   50  | 2015-05-18 11:25:54 | 1000   |
|   51  | 2015-09-18 12:26:22 | 3000   |
|   52  | 2015-05-18 13:48:59 | 10     |
|   53  | 2015-03-18 15:48:59 | 10     |
|   54  | 2015-01-18 17:49:13 | 5000   |
+-------+---------------------+--------+

I want to SELECT record with date

I Use

SELECT * FROM transactions WHERE date='2016-02-18';

I Also SELECT between 2 date And I Use

SELECT * FROM transactions WHERE date<'2016-02-18' AND date>'2016-02-01';

But Its Not Working.(I use php Mysql xampp) Can you help me understand the concepts?

What do you mean by "it's not working"? Are you getting an error message? Is it returning the wrong data? — Tom H
– Tom H, Commented Feb 18, 2016 at 13:35
What is the date column's type? timestamp/datetime or string? — mitkosoft
– mitkosoft, Commented Feb 18, 2016 at 13:37

devpro · Accepted Answer · 2016-02-18 13:57:06Z

2

For the ist query you need to use DATE() function becuase your column type is DATETIME or TIMESTAMP so you can handle it as:

SELECT * FROM transactions WHERE DATE(date) = '2016-02-18';

For second query you can simply add the TIME as:

SELECT * FROM transactions WHERE date > '2016-02-01 00:00:00' AND date < '2016-02-18 23:59:59';

edited Feb 18, 2016 at 13:57

answered Feb 18, 2016 at 13:44

devpro

16.1k3 gold badges29 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jay Blanchard Over a year ago

Are you his column is a DATETIME column? The OP never specifies that, even though the question was asked. :)

devpro Over a year ago

@jay-blanchard hello sir.. Yes u r 100% right. But in value I just saw the 24hours format and guess its DATE AND TIME.. mmaybe DATETIME or TIMESTAMP

ABHIJIT Over a year ago

How Can I Select Current or last month all transactions. Because All Months are not 30 days. So Its a big problem for me.

ThinkTank · Accepted Answer · 2016-02-18 13:38:49Z

1

Is it a mysql DB ?

If it is than use DATE function :

SELECT * FROM transactions WHERE DATE(date)='2016-02-18';

OR

SELECT * FROM transactions WHERE date BETWEEN '2016-02-18' AND '2016-02-01';

answered Feb 18, 2016 at 13:38

ThinkTank

1,1919 silver badges15 bronze badges

2 Comments

ABHIJIT Over a year ago

How Can I Select Current or last month all transactions. Because All Months are not 30 days. So Its a big problem for me.

ThinkTank Over a year ago

Sorry, I'm not sure to understand what you want. Maybe you should take an eye at mysql documentation to solve it : Mysql Date and Time functions. Or rephrase your need. (Or maybe you just need to use "MONTH()" function ?)

safin chacko · Accepted Answer · 2016-02-18 13:44:11Z

1

use this

use % and like in your query . you will get all results in given date

SELECT * FROM transactions WHERE date like '2016-02-18%';

edited Feb 18, 2016 at 13:44

answered Feb 18, 2016 at 13:41

safin chacko

1,3901 gold badge11 silver badges18 bronze badges

3 Comments

Jay Blanchard Over a year ago

Why should the OP "use this"? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO.

Jay Blanchard Over a year ago

While you made edits you didn't explain why the OP should use % and LIKE. You just told them to use it without any context. :)

ABHIJIT Over a year ago

How Can I Select Current or last month all transactions. Because All Months are not 30 days. So Its a big problem for me.

Vipin Jain · Accepted Answer · 2016-02-18 13:43:12Z

0

you should use BETWEEN ... AND ...

SELECT * FROM transactions WHERE `date` BETWEEN '2016-02-01' AND '2016-02-18';

edited Feb 18, 2016 at 13:43

answered Feb 18, 2016 at 13:36

Vipin Jain

3,77618 silver badges36 bronze badges

4 Comments

Jay Blanchard Over a year ago

Why should the OP "try this"? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO.

ABHIJIT Over a year ago

How Can I Select Current or last month all transactions. Because All Months are not 30 days. So Its a big problem for me.

Vipin Jain Over a year ago

@JayBlanchard where it is write you should not write 'try this' in answer. i have seen many user which have 300k and he write 'Try This' and 'use This' but no one can interfere so why you said. and you can comment on post but if my query is right so you haven't rights to down vote it. sorry if you hurt.

Jay Blanchard Over a year ago

I'm not hurt and if I saw a 300k user do the same thing I would make the same comment. Answers with explanations are always better. They server to teach not only the OP but others who will be reading the answer. My comment is not meant as interference, it is meant as guidance. It is your choice what you do with it. I did not down vote your answer, someone else did. I never down vote a valid answer.

Collectives™ on Stack Overflow

SELECT database data With Date

4 Answers 4

3 Comments

2 Comments

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related