Questions about the 'Time' column type

[oracleguy]

Okay I have a column setup in one of my tables as the 'time' data type.

It has a couple records like:
12:00:00
14:30:00
07:00:00
12:00:00

and when I view the data on my ASP page it puts this out:

1/5/2003 12:00:00 PM
1/5/2003 2:30:00 PM
1/5/2003 7:00:00 AM
etc...

The main question i had was why is it throwing the current date in there? And how do I get rid of it?

Also is there a way for it to remain in 24-Hour format?

The mySQL documentation wasn't very helpful.

[WA]

Are you saying what you view in the mysql prompt of the date field and what actually gets output on your page are different (one just the time, the other the full date)?

This doesn't address your problem directly, but in mySQL, you can format the output of the datetime field any way you want using date_format(). For example, to display only the time:

SELECT date_format(myfield, '%h:%i:%s') as formatteddate FROM mytable

Hope this helps,

[oracleguy]

Yeah thats sorta what I want but it doesn't seem to be working.

It returns 12:00:AM for 12:00:00 both times and for 07:00:00 its 12:00:AM.

The rest of the times like for 14:30:00 it returns nothing.

Here is the select statement i'm using:

SELECT date_format(itemTime, '%h:%i:%p'), itemDate, description FROM schedule ORDER BY itemDate, itemTime


And I'm talking about what I get as output on the page.

[WA]

The parameters you're using inside your date_format() function will format the time as:

Hours (0-12 padded) : Minutes (padded) : AM/PM

I gather what you want is:

date_format(itemTime, '%H:%i:%s:%p')

which would return:

Hours (0-23 padded) : Minutes (padded) : Seconds (padded):AM/PM

[oracleguy]

That wasn't exactly it but you got me moving in the right direction. :)

The reason it was returning strange values is that it should be time_format... not date_format.

Thanks for all your help :thumbsup: