Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6

Thread: database advice

  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    241
    Thanks
    6
    Thanked 0 Times in 0 Posts

    database advice

    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

  • #2
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    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.
    Leonard Whistler

  • #3
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    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.

  • #4
    Senior Coder Len Whistler's Avatar
    Join Date
    Jul 2002
    Location
    Vancouver, BC Canada
    Posts
    1,323
    Thanks
    26
    Thanked 100 Times in 100 Posts
    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.

    PHP Code:
    $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.
    Leonard Whistler

  • Users who have thanked Len Whistler for this post:

    tomws (03-29-2008)

  • #5
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    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.
    OracleGuy

  • #6
    Regular Coder
    Join Date
    Sep 2007
    Posts
    241
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •