SQL Help

[Axel]

Hi all,

 

Hopefully a quick one.  I want to select rows where the date is 1 days prior to the current day.

 

Trouble is the date is stored in a UK format as text (i.e. 17/06/2013).  Is this possible?

 

so far I have this which obviously wont work:

 
SELECT * FROM surveys WHERE date < CURRENT_TIMESTAMP - INTERVAL 1 DAY
 

Any ideas?

 

Many thanks.

 

Alex

[ILikeTobacco]

http://stackoverflow.com/questions/12831791/whats-the-sql-function-for-timestamp-manipulation

[Axel]

Sorry I'm not sure I understand that.  My knowledge in this area is still a little young. 

[ILikeTobacco]

Sorry I'm not sure I understand that.  My knowledge in this area is still a little young. 

Take the current timestamp, convert it to the same format as what is in the database, then run your query using that.

[Axel]

As much as I could do that easily using PHP, I'm not sure where to start with a MySQL query string??

[Kami-]

SELECT * FROM surveys WHERE date < (DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

May not be 100% accurate as it's typed direct into browser from memory; but should be fairly near the mark.

(Note if you plan on including the previous day, you should use <= ;))

[Haggis Veteran]

would it not just be = if your using interval of 1 day so like this?

SELECT * from surveys where date = DATE_SUB(CURRENT_DATE(),INTERVAL 1 DAY);

[Axel]

Thats brilliant thanks!  Is it possible to take a text field and treat it like a date?

 

i.e. the above works on a properly formatted column.  If the date was stored as text in UK format (dd/mm/yyyy) could one do some sort of manipulation so it was read as a date in the proper format (yyyy/mm/dd).  I know this can be done in php, I wonder if it would work in a mySQL query though?

[Kami-]

Thats brilliant thanks!  Is it possible to take a text field and treat it like a date?

 

i.e. the above works on a properly formatted column.  If the date was stored as text in UK format (dd/mm/yyyy) could one do some sort of manipulation so it was read as a date in the proper format (yyyy/mm/dd).  I know this can be done in php, I wonder if it would work in a mySQL query though?

 

Err...

DATE_FORMAT(date,'%Y/%m/%d')