Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Location
    AU
    Posts
    19

    Question Unanswered: Why use date or timestamp rather than number for dates?

    I need to store data which is coming from various streams as YYYYDOY and UTC. It is currently stored in the same way. Can you tell me why I should store it otherwise?

    cheers, calico

  2. #2
    Join Date
    Oct 2002
    Location
    NZ
    Posts
    25

    Re: Why use date or timestamp rather than number for dates?

    Store it anyway you like provided your method is consistent,

    This is the beauty of the relational approach. Your SELECT query can convert it into any format you want, in any case...

    ...you need to use TO_DATE and TO_CHAR functions using explicit date format strings to ensure you get the desired result.

    s.


    Originally posted by calico
    I need to store data which is coming from various streams as YYYYDOY and UTC. It is currently stored in the same way. Can you tell me why I should store it otherwise?

    cheers, calico

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Why use date or timestamp rather than number for dates?

    Originally posted by calico
    I need to store data which is coming from various streams as YYYYDOY and UTC. It is currently stored in the same way. Can you tell me why I should store it otherwise?

    cheers, calico
    Users may find the data more convenient if it is stored in a consistent DATE format - so for example to find all records less than a week old they can say "WHERE date1 < SYSDATE-7". It really depends on how the data is going to be used. If it is just going to be stored "as is" and not interrogated, fine (though in that case, why load it at all?). If it is being loaded in to be queried and reported against other data, you should convert it to DATE.

    To me, it is like saying that if a datafile to be loaded represents number values in a format like "999,999,999.999-" , then you are going to load it into VARCHAR2 variable rather than convert it to a NUMBER. I hope not!

Posting Permissions

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