Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Post Unanswered: Maxdate in table data (date range)

    Hi all, First post on THIS forum...so be kind (I have my nomex suite on anyway, and am flameproof, but STILL!)

    I have a table that consists of a bunch-o-gibberish, but essentially looks like this (for the sake of discussion):

    PK RecID int
    PK StartDate smalldatetime
    PK EndDate smalldatetime
    Gibberish_1 varchar(zillions and zillions)
    Gibberish_2 int

    OK, my query (get it? *LOL* I kill me) is this:

    My PK date range is essentially a range indicating a "validity date range" or in other words - "this record applys to dates from StartDate to EndDate".

    If I insert a new row, that I want to be "valid" from today on into the forseeable future. I'm thinking I would enter it with StartDate = today, and would like to figure out a good "MAXDATE" type value to put in the EndDate column so that:

    1) I can find a record using a single date (i.e., the current date) and find the validation record (as defined above) - - - I'm not concerned about this part of the question, as I can build the simple query to do so).

    2) NOT have to keep adjusting the EndDate to keep it ahead of the current date so that the query into the range will work.

    Essentially, my question is this: What is a good value to put in the EndDate to mean "infinity" - - that will still work when I query using a single date designed to find the validation record with a range that includes it?

    My boss suggested something like "January 1, 2040", but this bothers me, since my kids may be working here then :lol: and I don't want to have to force them into a Y2K-like issue where they now have to go adjust all the "maxdate" values. Anythoughts or conventions that I am unaware of here?

    Thanks!
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Since your end date column is small datetime the max value you can store is June 6, 2079. If you change your column to datetime, max date you can put is December 31, 9999.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks...I suppose that buys enough time that it would be the problem of my GRANDkids so that just may do...I was just hoping for something magical like MAXDATE, so it would never have to be changed.

    Oh well, it's just a typically @nal developer concern...typical for me...it just somehow seems WRONG on so many levels to put an essentially "random" date in as the high end of an open range.

    As if the database OR application will still be around in 2079 anyway *LOL*

    Thanks again for confirming my worst fears (well, besides the fear of opening that container of green stuff down in the back corner of my refridgerator, that is )
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I am leaving the EndDate field empty if it needs to be an open date, and in my queries still use BETWEEN:

    ...where @DateParameter between DateStart and isnull(DateEnd, @DateParameter)

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    But the problem is .. his app is not gonna work after June 6, 2079 coz the max date he can put into his start date is also smalldatetime
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Hmmm...I'll check that out...I wonder if it will work though if some of the records have a valid end date?

    My application will "never" have overlapping dates...but may have sequential ranges...such as:

    rec1 StartDate = 01/01/2002 EndDate = 02/01/2002
    rec2 StartDate = 02/02/2002 EndDate = 01/01/2004
    rec3 StartDate = 01/02/2004 EndDate = NULL

    will your query still work to find, say...12/31/2003 even though your query's top end is "isnull"??? I guess I'll give it a test and find out.

    Thanks!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Originally posted by Enigma
    But the problem is .. his app is not gonna work after June 6, 2079 coz the max date he can put into his start date is also smalldatetime
    *LOL* :smacking self on the forehead: There's always THAT, if you wanna get picky

    Problem solved...

    Though, I suppose (of Y2K taught us anything) that about May 31st, 2079 - they will change the algorithm to allow later dates...and I guess that's the brunt of my angst...I just HATE putting a "hard" date in a field that doesn't represent a hard date.

    Anyway, thanks for helping with the mental exercises this morning!
    Paul
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Originally posted by rdjabarov
    I am leaving the EndDate field empty if it needs to be an open date, and in my queries still use BETWEEN:

    ...where @DateParameter between DateStart and isnull(DateEnd, @DateParameter)
    Oops...sorry...misunderstood the function of the isnull function.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'd leave the end-date null, like rdjabarov suggested.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Yeah, I seriously thought about it, but the trouble with that is my primary key includes the EndDate (and so can't be NULL).

    However, that leads me to another question...does the EndDate NEED to be part of the key??? Hmmmm...if the selects on this table are going to be exclusively by a single date...i.e., each day I will build a child table row by first looking to this "validation" table by using the current date...am I losing performance by not including the EndDate in the key?

    My concern is that my BETWEEN clause will be less efficient if the enddate is not in the primary key.

    Also keep in mind that I will also be performing selects where the BeginDate and EndDate will actually have (historical - aka past...) dates in them (as noted in an earlier post).
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can exclude DateEnd from the PK, change the PK to non-clustered (unless you absolutely need it to be clustered), and create a clustered index with all three fields, which allows for a nullable column.

Posting Permissions

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