Formatting a SQL date into a UK format

Question

I have the following SQL query and I would like to output the ageOnLastSeptember date in a UK (dd/mm/yyyy) format. Can anyone tell me how I can achieve this?

$sql="select ( if(month(now()) < 9, year(now()) - 1, year(now())) - year(dob) - if(month(dob)<9, 0, 1)  ) as ageOnLastSeptember,first_name,last_name,dob,school,contact_no,family_id,contact_id from members_family where contact_id = '$contact_id' ";

I tried the following but it did nothing

$sql="select date_format(( if(month(now()) < 9, year(now()) - 1, year(now())) - year(dob) - if(month(dob)<9, 0, 1)  ), '%d/%m/%Y') as ageOnLastSeptember,first_name,last_name,dob,school,contact_no,family_id,contact_id from members_family where contact_id = '$contact_id' ";

This is a common problem. It has been asked and answered numerous time on this website already. Why should we add another answer? - http://php.net/datetime - http://php.net/strings — hakre, Apr 16 '13 at 07:25

score 2 · Answer 1 · answered Apr 16 '13 at 07:11

2

You do not need to do formatting at the database level. If you have dates stored as dates (the correct way), just get the records and send it to the client. Formatting should be done at the control which you are using to display these dates.

answered Apr 16 '13 at 07:11

danish

5,550
2
25
28

Thanks @danish. I tried displaying it within the PHP too and struggled. I am a newbie at all this. My previous post was incorrect. It's the DOB field I am trying to format. Here's my PHP code - ".$rows['dob']." – user2281899 Apr 16 '13 at 07:14
Sorry, I don't do PHP. I would suggest you tag your post as PHP and wait till someone helps you with date formatting in PHP. – danish Apr 16 '13 at 07:17
@user2281899 what is the current MySQL output? IN what format is it coming ? can you add that – Sabari Apr 16 '13 at 07:36
@Sabari it's 2002-05-18 – user2281899 Apr 16 '13 at 07:39
@user2281899 so how do you want it to display like 18/05/2002 or some other ? – Sabari Apr 16 '13 at 07:42
@Sabari - That's it. I want it displayed as 18/05/2002. Thanks – user2281899 Apr 16 '13 at 07:43

score 2 · Accepted Answer · answered Apr 16 '13 at 07:47

I think it will much easier and better to format the date using PHP rather than formatting using MySQL. After retrieving the data from MySQL you can format using PHP like

$date = date("d/m/Y", strtotime('2002-05-18'));

The output will be : 18/05/2002

So your PHP code will be :

<td>". date("d/m/Y", strtotime($rows['dob'])) ."</td>

More info on PHP date and its format can be found here: http://php.net/manual/en/function.date.php

Hope this helps you :)

score 0 · Answer 3 · answered Apr 16 '13 at 07:54

Not sure how an age can be formatted as a DD/MM/YYYY date. Doesn't seem logical

To get the age in days you could use something like the following

SELECT FLOOR(NOW(), DATE_ADD(dob, INTERVAL (ABS(DATEDIFF(CONCAT(YEAR(DATE_ADD(NOW(), INTERVAL -8 MONTH)),':09:01'), NOW())) * -1) DAY)) AS Age
FROM members_family 
WHERE contact_id = '$contact_id'

That could be customised to give an age in years (gets a bit trickier to be 100% accurate with leap years, especially if you want to cope with every 100th year not being a leap year but every 400th year being a leap year)

Formatting a SQL date into a UK format

3 Answers3