Tech Off Thread

12 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Question - What would you do?

Back to Forum: Tech Off
  • User profile image
    RamblingGeek​UK

    Customer wants to enter a year into the database as Pre-1960's, would you make that field a text or datetime within the database?

  • User profile image
    ZippyV

    I think SQL Server only goes back to 1700 or so. Does your customer want to go lower than that? If you need to do calculations with it, it might be better to use smallint or so (if date doesn't work).

  • User profile image
    spivonious

    SqlDateTime.MinValue is January 1, 1753, if that helps.

  • User profile image
    W3bbo

    Never use text for storing dates, if you do run into a problem where the MinValue for the built-in date/time value is too high, then store it as a 64-bit unsigned integer with the epoch set far enough in the past.

  • User profile image
    RamblingGeek​UK

    W3bbo said:

    Never use text for storing dates, if you do run into a problem where the MinValue for the built-in date/time value is too high, then store it as a 64-bit unsigned integer with the epoch set far enough in the past.

    not sure if I explained myself.  He acutally wants to type in "Pre 1960's" ... I don't reall see what choice I have.

  • User profile image
    spivonious

    Kryptos said:
    W3bbo said:
    *snip*

    not sure if I explained myself.  He acutally wants to type in "Pre 1960's" ... I don't reall see what choice I have.

    Oh, so he wants a date or "Pre 1960s"? Hmm...I think I might use a boolean and a date. If the boolean is true, then it's "Pre 1960s", if it's false then look at the date.

  • User profile image
    W3bbo

    Kryptos said:
    W3bbo said:
    *snip*

    not sure if I explained myself.  He acutally wants to type in "Pre 1960's" ... I don't reall see what choice I have.

    two DateTime fields, representing a range.

     

    If dt1 == dt2, then the date stored is a single point in time.

    If dt1 < dt2 then you've got a range, in this date dt1 == DateTime.MinValue, and dt2 = 1960-12-31

    If dt2 < dt1, then you can use that as an opportunity for some strange thing I can't think of.

     

  • User profile image
    Maddus Mattus

    Store year as an int if you dont need a datetime,..

     

    Storing DateTime if you only need year is overhead.

  • User profile image
    W3bbo

    Maddus Mattus said:

    Store year as an int if you dont need a datetime,..

     

    Storing DateTime if you only need year is overhead.

    The effect is negligible. an int is 32 bits, a datetime is 64 bits. On an x64 DBMS the int would be padded out to 64-bits to fit into a word size, so it's possible that the overhead is with using an int rather than a 64-bit type Wink

  • User profile image
    RamblingGeek​UK

    W3bbo said:
    Maddus Mattus said:
    *snip*

    The effect is negligible. an int is 32 bits, a datetime is 64 bits. On an x64 DBMS the int would be padded out to 64-bits to fit into a word size, so it's possible that the overhead is with using an int rather than a 64-bit type Wink

    thanks for your comments.....

  • User profile image
    Sven Groot

    W3bbo said:
    Maddus Mattus said:
    *snip*

    The effect is negligible. an int is 32 bits, a datetime is 64 bits. On an x64 DBMS the int would be padded out to 64-bits to fit into a word size, so it's possible that the overhead is with using an int rather than a 64-bit type Wink

    On an x64 DBMS the int would be padded out to 64-bits to fit into a word size

    That comment makes no sense whatsoever. For one thing, word alignment only really applies to memory, not persisted storage. For another, x64 still aligns ints on 32 bit boundaries.

  • User profile image
    Maddus Mattus

    W3bbo said:
    Maddus Mattus said:
    *snip*

    The effect is negligible. an int is 32 bits, a datetime is 64 bits. On an x64 DBMS the int would be padded out to 64-bits to fit into a word size, so it's possible that the overhead is with using an int rather than a 64-bit type Wink

    I don't mean overhead in storage mate Wink

     

    I mean overhead for dealing with a number, that is stored in a too complex data type for it's own good Smiley

     

    Year -> int

    Date -> DateTime

     

    Then you dont need to do nasty where statements like where datepart(myDate, yy) = 2010 etc etc

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.