how do i break it up?

kuehhc · January 18th, 2005, 03:52 AM

I have a field that records the time of record inserts.
I am trying to break up the time inserts into 3 parts for editing.

eg. 1:00 PM --> hr = 1, min = 00, symbol= PM

Is there a way to do it?

Thanks

BasilFawlty · January 18th, 2005, 11:31 AM

Is this a PHP timestamp, or a SQL field that you are trying to query?

kuehhc · January 19th, 2005, 12:07 AM

its a sql field.

BasilFawlty · January 19th, 2005, 09:52 AM

It depends on the DBMS that you are using. MySQL has a lot more built in date/time functions than SQL Server 2000 for example, but do a little research on your system to see what functions there are. This will work with all of the common ones..

If you are using MySQL, you can do it with just a single sql command:

Code:

select date_format(db_column, '%h') as hr, date_format(db_column, '%i') as min, date_format(db_column, '%p') as symbol from table_name

If its not MySQL
SQL statement:

Code:

Select hour(db_column) as hr, minute(db_column) as mn from table

Symbol Logic:

Code:

$symbol = "am";
if ($hr >= 12)
     $symbol = "pm";

Now you have all three parts to work with. Just be sure that it is of type datetime or time. Also note that some other MySQL functions have the ability to return hours > 24 if doing comparisons or other operations. This would change your logic here.

Hope this helps.

Jeff