Jump to content



Photo

SQL Help


  • Please log in to reply
8 replies to this topic

#1 Axel

Axel

    --[Est. 1986]--

  • Joined: 05-August 03
  • Location: Milton Keynes, UK

Posted 08 July 2013 - 18:31

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




#2 ILikeTobacco

ILikeTobacco

    Neowinian Senior

  • Joined: 08-July 10

Posted 08 July 2013 - 18:35

http://stackoverflow...mp-manipulation



#3 OP Axel

Axel

    --[Est. 1986]--

  • Joined: 05-August 03
  • Location: Milton Keynes, UK

Posted 08 July 2013 - 19:18

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



#4 ILikeTobacco

ILikeTobacco

    Neowinian Senior

  • Joined: 08-July 10

Posted 08 July 2013 - 19:20

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.



#5 OP Axel

Axel

    --[Est. 1986]--

  • Joined: 05-August 03
  • Location: Milton Keynes, UK

Posted 09 July 2013 - 06:50

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

#6 Kami-

Kami-

    ♫ d(-_-)b ♫

  • Tech Issues Solved: 3
  • Joined: 28-July 08
  • Location: SandBox

Posted 09 July 2013 - 07:27

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 <= ;))



#7 Haggis

Haggis

    Neowinian Senior

  • Tech Issues Solved: 16
  • Joined: 13-June 07
  • Location: Near Stirling, Scotland
  • OS: Debian 7
  • Phone: Samsung Galaxy S3 LTE (i9305)

Posted 09 July 2013 - 07:32

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);


#8 OP Axel

Axel

    --[Est. 1986]--

  • Joined: 05-August 03
  • Location: Milton Keynes, UK

Posted 09 July 2013 - 08:06

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?



#9 Kami-

Kami-

    ♫ d(-_-)b ♫

  • Tech Issues Solved: 3
  • Joined: 28-July 08
  • Location: SandBox

Posted 09 July 2013 - 10:04

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')