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

[Guest]

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

[bregent]

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

[Guest]

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

[Guest]

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.

[Guest]

iPHP,

Many thanks! I have to be honest, I have no idea what a sprint function is. This is an $sql query.

I'll still keep messing with it.

Thanks!

Cheers,

wordman

[bregent]

Sorry, I don't do MySQL so I'm not sure about any non standard syntax it may support, but this still looks wrong:

SELECT cal_dates

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

FROM cal_data

You are still selecting two columns the cal_date column and the formatted cal_date column. If you need both then comma separate them. Otherwise just try

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

FROM cal_data

[Guest]

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

[Guest · Accepted solution]

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']; ?>

[Guest]

iPHP,

I think I have too many queries posted here. I have tried many things in an effort to test what could be wrong, so let's start fresh.

This one works, sans DATE_FORMAT:

$sql = "SELECT page_ym, cal_dates, available, location, event
        FROM cal_data
        WHERE cal_data.page_ym = '$dateRef%'";

Again, I know this one has additional columns, but again, I've been trying many things since the original post and this is the format I'll be wanting to use.

Anyhow, as soon as I add DATE_FORMAT to the query, AND change the alias:

$sql = "SELECT page_ym, cal_dates, available, location, event
        DATE_FORMAT(cal_dates, '%c/%e/%Y') AS cdate
        FROM cal_data
        WHERE cal_data.page_ym = '$dateRef%'";

it bombs. I have used the column name as well as a different name for the alias, same thing.

I'm wondering if I whould use a seperate query just to pull the dates?

Thanks!

Cheers,

wordman

[Guest]

as soon as I add DATE_FORMAT to the query, AND change the alias:

$sql = "SELECT page_ym, cal_dates, available, location, event
        DATE_FORMAT(cal_dates, '%c/%e/%Y') AS cdate
        FROM cal_data
        WHERE cal_data.page_ym = '$dateRef%'";

it bombs.

There isn't a comma after your last table field (event in the example's case) and the date format function. Try this:

        $sql = "SELECT page_ym, cal_dates, available, location, event,
        DATE_FORMAT(cal_dates, '%c/%e/%Y') AS cdate
        FROM cal_data
        WHERE cal_data.page_ym = '$dateRef%'";

Then echo the cdate on the page to view the formatted date. That should work.

[Guest]

iPHP,

Our posts passed in mid-stream...your suggestion worked perfectly!

Thank you!!!

Sincerely,

wordman

[Guest]

iPHP,

You win the prize!

ALL it was was the extra comma you mentioned:

$sql = "SELECT page_ym, cal_dates, available, location, event, <--- (the one after event)
        DATE_FORMAT(cal_dates, '%c/%e/%Y') AS cdate
        FROM cal_data
        WHERE cal_data.page_ym = '$dateRef%'";

That stopped the page from bombing!

IT WORKS!!!

One comma!

THANK YOU!!!

Sincerely,

wordman

[bregent]

>You win the prize!

Didn't I mention that you were missing commas from your select list in both of my replies? Don't I win anything?

[Guest]

bregent,

Sorry, I missed the prize comment.

Of COURSE you win the prize also! Please keep in mind, I have literally been coding for 10 days straight between database work and implementing FMIS, my head has been pushed to its max. I want you to know that I made sure you and iPHP received 'helpful answer' credits every step of the way. You have helped me solve many problems here and for that, you will always be a valued friend!

Yes, you and iPHP have jointly saved the day!

Sincerely,

wordman

PS - got any tips for the question I posted about breaking up the date into individual components?

[bregent]

>No, there's only one column reference, cal_dates.

>cal_data is the table from which I'm selecting.

I  didn't say you were referencing more than one column, only that you  were selecting more than one column. You were selecting the cal_dates column, and then the formatted cal_dates column. They may be referencing the same table column but they are two columns in your select list. All columns in a select list need to be comma separated. End of story.

[Guest]

bregent,

Gotcha. As this one's solved, and as my temples are pounding from days' worth of trying to figure it out, we'll leave it at that.

You always are a great help and I appreciate your input!

Sincerely,

wordman