[RESOLVED] DATE_FORMAT help

perpetualshaun

This should be VERY simple, but for some reason I've been looking at my query and the results for a while now, and I simply can't see anything wrong. I want to add a column to my results w/ a formatted date, instead of the timestamp value:

SELECT * , DATE_FORMAT( `sch_event_starting_timestamp` , '%Y-%m-%d' ) AS yyyymmdd
FROM `dc_s_events_v2_manage`  LIMIT 0 , 30

For some reason, ALL my results have a NULL in the last column, but that sch_event_starting_timestamp does indeed have a valid timestamp value. ???

Any ideas?

Thanx,
Shaun

bpat1434

How are you referencing it? What's your code look like?

~Brett

perpetualshaun

When I run that query directly through the PHP MyAdmin, it's showing me all NULLs in that last column. I'm pulling from an event listing table, and all 300+ events are coming through w/ NULL instead of the formatted date that I'm looking for.

bpat1434

have you tried not selecting * but just this:

SELECT DATE_FORMAT(sch_event_start_timestamp, '%Y-%m-%d') AS yyyymmdd FROM dc_s_events_v2_manage LIMIT 0 , 30

Maybe?

Also, is the column type in the table a time-stamp (the one with the timestamp in it)?

~Brett

perpetualshaun

I had tried just selecting that one field, but w/ the same result.

However, the field that I am trying to convert is NOT the TIMESTAMP format. This is probably something that I should have learned a long time ago, but I have always set my timestamp fields in MySQL to a VARCHAR type w/ a length of 14. Why? Well, I remember when I first starting working w/ PHP and MySQL years ago a co-worker told me that if you set a field as timestamp, then you cannot write to it, or pull from it and this is what he did. Sure enough, when I just converted that field to TIMESTAMP through PHP MyAdmin, it erased all my values! So what is the proper way to do this?

The sch_event_starting_timestamp is a timestamp that I'm creating w/ code based on the users date and time selection boxes.

bpat1434

Okay, that is misinformation. You CAN write to a TIMESTAMP column. You just have to write to it in its proper format.

My guess would be you stored them using the unix timestamp, which MySQL doesn't like in a TIMESTAMP field. If you want to use that query, change the data format in the column to be in this form: year month day hours minutes seconds.

Here, go to this page and try it out:
Add A Date
View the results

The database setup is this (very simple):
id int(11) NOT NULL auto_increment,
timedate datetime default NULL

Proves that you CAN write to the column. MySQL Explanation of Date & Time Types. If you read that, you should be able to better help yourself.

~Brett