database advice

[dajohnson1s]

I am currently working on my senior project for college, we are making a scheduling system to be used by a real company.

I am currently having some trouble getting a datetime value to display correctly (when i pull the value from the db it reads from right to left) I dont see any setting of how to store this value in the database. But since I am only wanting the time portion and could care less about the date, another forum had suggested "best practice" is to not use datetime.

I am having trouble convince another team member to change the database to varchar or string...could anybody propose reasons to keep datetime or to switch?

thanks

[Len Whistler]

I would keep it as datetime in case you need the full date later on. If all you need is the time you can use php to extract the last x amount of characters from the time column, in this case 8 or so.

[tomws]

The best reason to use datetime is that you're storing a datetime. Databases aren't much more than big garbage cans if everything is just stored as varchar.

Which dbms are you using? Datetime result is right to left? If the desire to change the field type is based solely on the result format, code up a converter of a dozen lines and drop it into a function.

[Len Whistler]

I checked my PHP/MySQL book and the best solution is to use a DATETIME MySQL column and then SELECT DATE_FORMAT to format the output.

$test = mysql_query("SELECT DATE_FORMAT(reply_time, '%r') FROM replies");
while($row = mysql_fetch_array($test)){
echo "$row[0]<br />";
}

The above code will output 2008-03-23 16:12:44 as 04:12:44 PM.

[oracleguy]

It is important to use the datatime column type so that you can efficiently do comparisons on it in your SQL statement as well. If you make it a varchar, you aren't going to be able to do a select where the date is in between two dates or after/after another date or anything like that.

[dajohnson1s]

Thanks for all the responses.

the requirements for the project are to have a sql server using asp.net 2.0.

I had gotten everything to finally display the way I had wanted with formatting.

But I was just curious what the "real world" best practice was, for when I graduate and all.

thanks