How to get the values between particular date in mysql

Question

I want to get the values between particular date and time. But each values - time, hour and minutes are there in separate fields.

Following is the table format i have,

Date        Hour    Min     values

1/1/2011    10      30      Test1
2/1/2011    8       10      Test2
3/1/2011    15      40      Test3
5/1/2011    11      10      Test5
10/1/2011   3       04      Test6
12/1/2011   5       00      Test6

Now i want to get the "values" between '3/1/2011 12.00' and '10/1/2011 11.10' using MYSQL SELECT query. If anyone knows pls help me

show us your try query..

Jenish
– Jenish

2017-06-30 08:51:36 +00:00
Commented Jun 30, 2017 at 8:51 — Jenish
– Jenish, Commented Jun 30, 2017 at 8:51
You shouldn't store your datetime in such format

Jens
– Jens

2017-06-30 08:58:09 +00:00
Commented Jun 30, 2017 at 8:58 — Jens
– Jens, Commented Jun 30, 2017 at 8:58
Store dates and times as a single entity.

Strawberry
– Strawberry

2017-06-30 10:49:03 +00:00
Commented Jun 30, 2017 at 10:49 — Strawberry
– Strawberry, Commented Jun 30, 2017 at 10:49

Mayank Pandeyz · Accepted Answer · 2017-06-30 08:55:32Z

2

Updating @Suresh answer, as the default date format is 'Y-m-d'.

SELECT values
FROM table_name
WHERE `Date` between '2011-01-03' and '2011-10-01'

answered Jun 30, 2017 at 8:55

Mayank Pandeyz

26.3k4 gold badges43 silver badges61 bronze badges

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

Comments

Suresh Kamrushi · Accepted Answer · 2017-06-30 10:05:05Z

2

You can do like this:

SELECT id, CONCAT(dateval,' ',hourval,':',minval,':00') as t 
 FROM ForgeRock 
 WHERE CAST(CONCAT(dateval,' ',hourval,':',minval,':00') as DATETIME) 
  between CAST('2006-01-21 17:10:00' as DATETIME) AND 
   CAST('2006-01-25 22:10:00' as DATETIME)

Because of concate() we need to use CAST to convert them as datetime. SQL fiddle for it: http://sqlfiddle.com/#!9/4a072/12

edited Jun 30, 2017 at 10:05

answered Jun 30, 2017 at 8:52

Suresh Kamrushi

16.2k13 gold badges79 silver badges93 bronze badges

8 Comments

KMS Over a year ago

I need to include time also in where condition, then only i will get correct output.

KMS Over a year ago

Thanks for your response. I will check @Suresh Kamrushi

KMS Over a year ago

Suppose my query is, SELECT values FROM table_name WHERE CONCAT(date,' ',Hour,':',Min,':00') between '3/1/2011 12.00' and '10/1/2011 11.10' then it is returning 3/1/2011 11.00 value too.. @Suresh Kamrushi

Suresh Kamrushi Over a year ago

share your query

KMS Over a year ago

SELECT values FROM table_name WHERE CONCAT(date,' ',Hour,':',Min,':00') between '2011/1/03 16.00' and '2011/1/10 11.10'

|

harsh kumar · Accepted Answer · 2017-06-30 11:06:59Z

0

We can also use alternate of Between..

SELECT *
FROM table
WHERE Date >= '2011-01-03' and Date < '2011-10-01'

edited Jun 30, 2017 at 11:06

answered Jun 30, 2017 at 10:49

harsh kumar

1658 bronze badges

Collectives™ on Stack Overflow

How to get the values between particular date in mysql

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related