Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    Unanswered: Char(4) or integer for Year / Quarter Field

    Hello,

    what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?

    Both are part of a composite index.

    Thanks
    Silas

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "resp." mean?

    best for a numeric year like 1937, 2007, etc. is SMALLINT

    best for quarter (1-4) is TINYINT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    183
    Why not combine them both?

    SMALLINT as 20074 (yyyyq)
    this will work upto year 3276 and quarter 4.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why not? because how would you pull out the rows for 2007 that way?

    that's why not combine them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Posts
    97
    Quote Originally Posted by r937
    why not? because how would you pull out the rows for 2007 that way?

    that's why not combine them
    That's why :-)

    resp. = respectively , isn't it?

  6. #6
    Join Date
    Apr 2007
    Posts
    183
    I thought this could pull all records for 2007

    Col1 >= 20071 And Col1 < 20081

    but then again, I might be wrong.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that works, but it's clumsy

    now show me how to get all the rows for the 3rd quarter only

    clumsier and clumsier!!

    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
  •