Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Should I use DATE type?

    Hi,
    I'm currently making a website that uses MySQL, and creates a new row for each day that the site is up. I've been using DATE for the testing so far, but I'm using the value as an output, and I don't like the format year-month-date. Can I either change the way the date is stored, to allow me to have it in the format day-month-year, or would I just have to use a VARCHAR to have it in this format? I'm assuming using a VARCHAR wouldn't let me pull the results in date order. However, if they're added in the correct order, it could work then?
    I'm new to working this close the databases, so I might have some ideas here a bit wrong!
    Thanks for any help,
    Joseph Duffy

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Never ever store a date in a VARCHAR column, it's more trouble than it's worth.

    If you don't like the format, simply format the date value inside your application (using whatever your programming language offers) or retrieve it in a formatted way in your SQL statement using the date_format() function:

    http://dev.mysql.com/doc/refman/5.5/...on_date-format

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Dates are not stored as text. They are stored as a numeric value. How the date is presented in your SQL statement is handled by DATE_FORMAT function as mentioned above.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to echo others comments
    store the values in a column of the appropriate type
    so dates in datetime columns
    numbers in an appropriate numeric column. (intgers in an integr type (tinyint,bigint,integer)
    ..and so on

    formatting a column can be done when you extract the data eitehr in the query or the data consumer

    with dates there is another especially good reason to use the datetime datatype and that is you can make use of the date time functions built into MySQL
    MySQL :: MySQL 5.5 Reference Manual :: 11.7 Date and Time Functions

    right now you may only need the date values to appear on a report, but who knows if you may want to, say do a summary report based on month or year.
    who knows if you want to only extract data for a specific month or a specific time period. storing the values in their proper datatype now elimiantes any possible problems down the line.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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