PHP MySql Date

[KobusDJ]

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.

[dyanamo]

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.

[Kloon]

SELECT YEAR(datefield) AS theyear FROM table

$row['theyear']

[KobusDJ]

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.

[dyanamo]

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.

Just replace YEAR with MONTH or DAY :)

[KobusDJ]

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

[dyanamo]

Rather than getting into mysql functions rather use the substr meathod especially if you want to retrieve multiple fields.

[KobusDJ]

I have already implemented the substring method.

Just curious why the sql function doesn't work.

I found this as well (http://www.simplemachines.org/community/index.php?topic=22517.0)

[dequadin]

I have already implemented the substring method.
Just curious why the sql function doesn't work.


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...

[KobusDJ]

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.

[dequadin]

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.

Looks like that is not the case (http://lists.mysql.com/win32/16795)!

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...

[KobusDJ]

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.

[dequadin]

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.

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

[KobusDJ]

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.

[dequadin]

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.

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



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 (http://dev.mysql.com/doc/refman/5.0/en/select.html), this (http://www.techotopia.com/index.php/Working_with_Dates_and_Times_in_MySQL), this (http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_extract) and this (http://innovationsopen.com/?p=48).

Something you could try is:


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;

[KobusDJ]

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 )

[dequadin]

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 )

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.

[FarligOpptreden]

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...

OOH! I absolutely hate substringed dates. In SQL Server, datediff and dateadd are your best friends...

[dequadin]

OOH! I absolutely hate substringed dates. In SQL Server, datediff and dateadd are your best friends...

Where were you yesterday when I was fighting the good fight? :D

[FarligOpptreden]

Where were you yesterday when I was fighting the good fight? :D

Earning my salary... ;)

[dequadin]

Earning my salary... ;)

Hahahaa good answer! :cool: