Query Sql Date and Time(CLOSED)

Question

Please Help me. i have table A. date format is dd/mm/yy and time format is hh:mm:ss am/pm

╔══════════╦═════════════╦═════════════╦═════════╗
║   DATE   ║    TIME     ║    PHONE    ║ MESSAGE ║
╠══════════╬═════════════╬═════════════╬═════════╣
║ 25/09/07 ║ 01:57:55 PM ║ 62856979898 ║ Tes a   ║
║ 25/09/07 ║ 04:56:51 PM ║ 62856123467 ║ Tes B   ║
╚══════════╩═════════════╩═════════════╩═════════╝

How query to find data where date between time.

http://stackoverflow.com/questions/4125931/mysql-combining-date-and-time-column-into-a-time-stamp — Dipesh Parmar, Mar 25 '13 at 08:43

John Woo · Answer 1 · 2013-03-25T08:52:23.340

This is one of the disadvantages of not storing the data type properly on the database. Dates should be stored as DATE or DATETIME.

If you are using MySQL, you can use STR_TO_DATE which converts string into date,

SELECT  *
FROM    tableName
WHERE   STR_TO_DATE(CONCAT(date, ' ', time), '%d/%m/%y %h:%i:%s %p') 
        BETWEEN '2007-09-25 13:00:00' AND '2007-09-25 14:00:00'

OUTPUT

╔══════════╦═════════════╦═════════════╦═════════╗
║   DATE   ║    TIME     ║    PHONE    ║ MESSAGE ║
╠══════════╬═════════════╬═════════════╬═════════╣
║ 25/09/07 ║ 01:57:55 PM ║ 62856979898 ║ Tes a   ║
╚══════════╩═════════════╩═════════════╩═════════╝

Some disadvantages of your current design:

cannot directly compare dates and times
cannot use index
etc ...

score 0 · Answer 2 · answered Mar 25 '13 at 08:49

0

use BETWEEN keyword

 "SELECT * FROM table_A WHERE date BETWEEN '" . $from_date . "' AND  '" . $to_date . "'"

answered Mar 25 '13 at 08:49

웃웃웃웃웃

11,829
15
59
91

Those dont look like date fields. Also, it is better to use prepared statements. – ulentini Mar 25 '13 at 09:18

Query Sql Date and Time(CLOSED)

2 Answers2