-2

In my SQL database Valid Up To : Thursday, January 01, 2015 3:17 AM is stored as string with column name valid time. Now I want to select data which are updated in last one hour. How can I make a suitable query?

halfer
  • 18,701
  • 13
  • 79
  • 158
Anwar Husain Warsi
  • 11
  • 2
  • It would be ideal if you could convert the type of this to `DATETIME`, as it will make temporal data easier to work with. Is that possible in this application? Why do you store the string "Valid Up To" as well? It would make much more sense to omit that, since the only purpose of this field is a validity timestamp. – halfer Apr 08 '15 at 09:59
  • Actually database was made by someone else it is storing as string though a c# program. now i want to handle with this database without disturbing its originality. – Anwar Husain Warsi Apr 15 '15 at 10:48
  • Fair enough. Well, you can start by searching for the things you need (as I have done for you just now, in this very site). Trim out the "Valid Up To : " string using [MySQL's replace function](http://stackoverflow.com/questions/5956993/mysql-string-replace). Then you can search for [MySQL date parsing](http://stackoverflow.com/questions/3296725/parse-date-in-mysql). – halfer Apr 15 '15 at 11:42

1 Answers1

0

You can parse the string and convert it to a date with STR_TO_DATE(). Parsing options are described here.

SELECT
*
FROM your_table
WHERE STR_TO_DATE(`valid time`, 'Valid Up To : %W, %M %d, %Y %l:%m %p') >= NOW() - INTERVAL 1 HOUR;
fancyPants
  • 46,782
  • 31
  • 80
  • 91