PHP MySql Date

K

KobusDJ

Well-Known Member
Joined
Oct 9, 2008
Messages
155
Reaction score
0
Location
Gaborone
I have a MySql table with a date field.

I can display the date: echo $row['TheDate'];

but need to know the year.


How do I get the Year value (e.g. 2009) from: $row['TheDate'];

Thanks.
 
The quickest and simplist way I can think for you is to do :
year = substr($row['TheDate'],0,4);
I say this since mysql (if using the date type) store the date in the format 2009-10-10.
 
Thanks dyanamo. The string manipulation works. This way I can get the month and day as well. Super!

Kloon, is there an equivalent for the MONTH and DAY? Your example works well to get the YEAR.
 
Last edited:
Ok, YEAR, MONTH and DAY works fine individually, but how do I get all three in one SELECT.

Something like this:
SELECT YEAR(TheDate) AS TheYear, MONTH(TheDate) AS TheMonth, DAY(TheDate) AS TheDay FROM MyTable


....doesn't work
 
Rather than getting into mysql functions rather use the substr meathod especially if you want to retrieve multiple fields.
 
KobusDJ said:
I have already implemented the substring method.
Just curious why the sql function doesn't work.
Click to expand...

The substring method is dangerous because it could give varying answers depending on the Regional/Language options that are set-up.

I speak purely from a Windows Development point of view. Rather get the SQL Build in functions to work, could save you some hassle later...
 
I do not see a problem with extracting the string.

The php is running on a Linux server and AFAIK (I may be wrong), the format in a Date field in MySQL is always YYYY-MM-DD.

Only time there will be problem, is when my host (LunarPages) starts to stuff around with the date format on their webservers.
 
Last edited:
KobusDJ said:
I do not see a problem with extracting the string.

The php is running on a Linux server and AFAIK (I may be wrong), the format in a Date field in MySQL is always YYYY-MM-DD.

Only time there will be problem, is when my host (LunarPages) starts to stuff around with the date format on their webservers.
Click to expand...

Looks like that is not the case!

Why are you so against doing things properly? Code like this (substring-ing a year out of a date) rubs me up the wrong way...
 
Do you know why.....

SELECT YEAR(TheDate) AS TheYear, MONTH(TheDate) AS TheMonth, DAY(TheDate) AS TheDay FROM MyTable

..... doesn't work.

I'm not changing the date format on the server..... the string will work for now.

EDIT: I see the link you posted is on the MySQL on Win32 section.
 
Last edited:
KobusDJ said:
Do you know why.....

SELECT YEAR(TheDate) AS TheYear, MONTH(TheDate) AS TheMonth, DAY(TheDate) AS TheDay FROM MyTable

..... doesn't work.

I'm not changing the date format on the server..... the string will work for now.
Click to expand...

I did read the thread before I posted so I know that it didn't work for you.

What TYPE is your TheDate column? Maybe you need to cast it first?

Something like
SELECT YEAR(str_to_date(TheDate )) AS theYear FROM MyTable
 
The TheDate field is of type 'Date' in the MySql table.

SELECT YEAR(TheDate) AS TheYear FROM MyTable ....works fine
SELECT MONTH(TheDate) AS TheMonth FROM MyTable .... works fine
SELECT DAY(TheDate) AS TheDay FROM MyTable ... works fine

The problem comes when I attempt to get all three values in on SELECT statement.
 
KobusDJ said:
The TheDate field is of type 'Date' in the MySql table.

SELECT YEAR(TheDate) AS TheYear FROM MyTable ....works fine
SELECT MONTH(TheDate) AS TheMonth FROM MyTable .... works fine
SELECT DAY(TheDate) AS TheDay FROM MyTable ... works fine

The problem comes when I attempt to get all three values in on SELECT statement.
Click to expand...

Had a quick look around on Google and it should be possible, so your problem is elsewhere.

Code: 
SELECT YEAR(your_date) Y, MONTH(your_date) M, DAY(your_date) D FROM your_table;
Is a perfectly valid MySQL query according to this, this, this and this.

Something you could try is:
Code: 
SELECT EXTRACT(YEAR FROM your_date) AS Y, EXTRACT(MONTH FROM your_date) AS M, EXTRACT(DAY FROM your_date) AS D FROM your_table;
 
Mmmmm.... I retyped the procedure from scratch at home tonight. Sql query works fine with all three in the SELECT.

Will have to drink more coffee at the office.

Thanks everyone for the help.... (and persistency to do it the PROPER way :D )
 
KobusDJ said:
Mmmmm.... I retyped the procedure from scratch at home tonight. Sql query works fine with all three in the SELECT.

Will have to drink more coffee at the office.

Thanks everyone for the help.... (and persistency to do it the PROPER way :D )
Click to expand...

Glad you got it right. It's always worth it to spend extra effort and to try and do things properly, will have you a lot of headache in the long run.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X