Skip to main content
A
Anonymous
October 30, 2010
Answered

I can NOT make DATE_FORMAT work...any ideas?

  • October 30, 2010
  • 1 reply
  • 1700 views

Hello all,

In reviewing David Powers' fantastic book, 'PHP Solutions', I decided to do some work with dates in a database.

The table where the dates are stored is called cal_dates and is DATE type.

All the dates are stored as yyyy-mm-dd

When I write my query and add DATE_FORMAT, the page bombs no matter what I try.

As in:

SELECT *
DATE_FORMAT(cal_dates, '%c/%e/%Y') AS cal_dates
FROM cal_data

and I get a white screen.

So, I figured perhaps I should change the alias (the example in Pwers' book on page 390 does not use a different alias, but after struggling with this for almost 2 days, I am now trying anything):

SELECT *
DATE_FORMAT(cal_dates, '%c/%e/%Y') AS cdate
FROM cal_data

and I get another white screen.

Originally, my query was designed with a WHERE clause because I am selecting data from one table based on an a variable, but I read somewhere that DATE_FORMAT should not be used with WHERE...which is funny, since the two examples shown above with WHERE omitted still bomb.

The original query:

SELECT *
FROM cal_data

WHERE cal_data.page_ym = '$dateRef%'

works PERFECTLY. It pulls all information in the table relevant to the $dateRef variable.

The reason I wanted to use DATE_FORMAT is because I wanted to take the dates in my table from yyyy-mm-dd and display them in a different format.

But to no avail. Any thoughts or musings would be appreciated. I'm stonewalled.

Thank you all!

Sincerely,

wordman

This topic has been closed for replies.
Correct answer

Bregent,

No, there's only one column reference, cal_dates. cal_data is the table from which I'm selecting.

Remove the DATE_FORMAT and this query works fine.

Cheers,

wordman


I think bregent is onto something there... If the only field in cal_data is the cal_dates then really there's nothing to select, only a date to format. Also the cal_dates should be named AS something other than the name it's formatted from. Try this: omitting the SELECT statement and renaming the cal_dates AS the_date

DATE_FORMAT(cal_dates, '%c/%e/%Y') AS the_dates

FROM cal_data

If that doesn't work then create a primary key, auto-increment field in cal_data table. Name the field id so that the query has something to select before it formats the date like this:

SELECT id, DATE_FORMAT(cal_dates, '%c/%e/%Y') AS the_dates

FROM cal_data

I forgot... put a comma after the last field selection and the DATE_FORMAT function in your query like the example above and make sure you're echoing the_dates on the page to see the formatted date displayed.

<?php echo $rs_name_row['the_dates']; ?>

1 reply

B
bregent
Participating Frequently
October 30, 2010

Looks like invalid SQL to me. You're doing a Select * and then adding another column. Listed columns need to be comma separated.

A
Anonymous
October 30, 2010

Bregent,

Hello again!

My screw-up. I copied the query from an earlier page.

This doesn't work either:

SELECT cal_dates

DATE_FORMAT(cal_dates, '%c/%e/%Y') AS cal_dates

FROM cal_data

Sorry for the mistake. Thoughts?

Cheers,

wordman

A
Anonymous
October 30, 2010

sprintf("SELECT cal_dates

DATE_FORMAT(cal_dates, '%%c/%%e/%%Y') AS the_date

FROM cal_data WHERE table_field = %s, GetSQLValueString($table_field_variable, "text"));

If you're using DATE_FORMAT function inside of a sprintf() function then add an extra % to each date element you want to format in the function. Don't name the date as the same name. Rename the date to something else, like the_date.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices