Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30

    Question Unanswered: Are there any good reasons to define a date column as a string (varchar2) type?

    I am designing the data model of an application, and I remember that I've seen in previous jobs that data columns were defined as string types, not as dates. The reason for this is not clear - it seems it may be simpler to sort by date columns at the Presentation Layer (C#, for instance. Just a simple string sort (using the format YYYYMMDD).

    But thinking about it makes me wonder what can be so bad in opting for DATE type, instead. Sorting should not be a problem, not at the DB side, not at the front-end (would only need to convert the column to DateTime there and that's it...)

    Any insights on should be better?

    Thanks
    Last edited by 435 Gavea; 01-19-14 at 04:35.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Oracle® Database SQL Language Reference 11g Release 2 (11.2)
    -> 19 SQL Statements: SAVEPOINT to UPDATE -> SELECT

    ...
    ...
    ...

    order_by_clause

    ...
    ...

    expr expr orders rows based on their value for expr. The expression is based on
    columns in the select list or columns in the tables, views, or materialized views in the
    FROM clause.
    Orders can be specified based on columns in tables as well as based on columns in select list, whichever meet your requirement.
    So, sort order might be not the reason to use string type.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,113
    Provided Answers: 5
    I don't see any valid reason to store date values into anything but DATE (or, optionally, TIMESTAMP) datatype columns.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Are there any good reasons to define a date column as a string (varchar2) type?
    only amateur or fools store DATE as string or number.
    DATE datatype ensures that only valid data is stored as a column value.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and just to add to Anacedent's pithy observation, using the correct datatype to represent temporal values means you can use Oracles built in date/time functions
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Using one datatype to store values from another datatype (such as using a string to store a date) provides great opportunity for consultants and hardware vendors. The database will perform poorly compared to expectations and equivalent databases that use the appropriate types, so this creates an endless source of opportunities for consultants and salespeople!

    There is no benefit to using an inappropriate data type for the user or the client, but it provides all kinds of opportunities for others!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by Pat Phelan View Post
    Using one datatype to store values from another datatype (such as using a string to store a date) provides great opportunity for consultants and hardware vendors. The database will perform poorly compared to expectations and equivalent databases that use the appropriate types, so this creates an endless source of opportunities for consultants and salespeople!

    There is no benefit to using an inappropriate data type for the user or the client, but it provides all kinds of opportunities for others!

    -PatP
    +1 (thankfully!)

  8. #8
    Join Date
    Jul 2011
    Location
    Nasik, Maharastra
    Posts
    34
    There are no any good reasons to do so.
    SHARING KNOWLEDGE IS ULTIMATE KEY TO GAINING KNOWLEDGE...
    dbatricksworld.com

Posting Permissions

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