wrong date format used for mysql - how to fix it

[unwashed potato!]

i have the date saved as a variable and in the format of DD/MM/YYYY on my database, however for me to use DAYOFWEEK on the date, this format doesn't work and it needs to be YYYY-MM-DD.

it'll be too much work to go back and edit the way the date is saved, and then all the place it's queried and used, so hoping to find a solution with what i've got.

is there a way to make the below work.

$daySent = 1

$sql="select count(DISTINCT(postcode)) as total from quotes WHERE DAYOFWEEK(date) = '$daySent' ";

i'm basically looking to count how many quotes we've ever had for fridays, wednesdays etc.

thanks

 

[AHarvey]

You may be able to use the following:

DATEFORMAT()
CONVERT()

 

[touch]

As AHarvey said, you could use CONVERT:

$daySent = 1

$sql="select count(DISTINCT(postcode)) as total from quotes WHERE DAYOFWEEK(CONVERT(date, DATE)) = '$daySent' ";

 

[unwashed potato!]

thanks guys. seems to have got it working

 

[visibleman]

it'll be too much work to go back and edit the way the date is saved..

You should be using DATE types for those fields as it vastly more efficient (joins, groups etc) and will just hinder you further down the line as you've found out.

As for it being too much work to convert, it really isn't - new column with correct type; CONVERT to YYYYMMDD and copy from old to new column; wrap DATE_FORMAT() around current 'date' column select in queries (simple replace in any IDE/editor, ie - SELECT `date` to SELECT DATE_FORMAT(`date`, '%d/%m%Y')). Just backup data prior to carrying it out.