Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2006
    Posts
    4

    Unanswered: Table design and querry

    I have a simple database that has 4 fields.

    Auto number for the key,
    Date (Not sure the best format)
    invoice number, (General number)
    Description. (Text field)

    My question is what is the best way to format the date as there will be only one query used in this data base.

    I want the query to return all invoices for the current week, past and present.
    So it would return invoice for the cureent week this year and the same week of previouse years



    Hope this makes sense.
    Any help is appreciated

    Thanks

    Pat

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by pmk1031
    I have a simple database that has 4 fields.

    Auto number for the key,
    Date (Not sure the best format)
    invoice number, (General number)
    Description. (Text field)

    My question is what is the best way to format the date as there will be only one query used in this data base.
    Date format only affects display. The internal format is a 64 bit Julian date... I forget when the epoch is.

    Only formatting recommendation I'd make is to use a 4 digit year.

    Also, consider making the invoice number a text field if you need to distinguish leading zeroes. Look into doing an input mask, too.

  3. #3
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    I agree with Sco08y on the text field for the invoice number (even though it wasn't your original question)

    General rule (as it was taught to me by one of my professors many years ago) was that if you did not need to perform any sort of computation on the datafield, then it should be a text field vice a number field.

    One of the other advantages besides distinguishing leading zeroes is if, for whatever reason, the spec for the invoice numbers should change and you would be required to use non-numeric data.. i.e. A12346Z. By having it as a text field right from the getgo, you have the flexibility to change the input criteria with no real impact on the structure of your database.

    -Friz

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sco08y
    Only formatting recommendation I'd make is to use a 4 digit year.
    when displaying dates for human beings, i believe the formatting styles which have the absolute least ambiguity involve using a 3-char month name as well

    there will always be ambiguity if you display 05/06/2007 versus 06/05/2007 and yes, even 2007-05-06

    but there is no ambiguity in 06 May 2007 or 2007 May 06 or May 06 2007
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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