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!
PaulSince 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.|||Thanks...I suppose that buys enough time that it would be the problem of my GRANDkids :D 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 ;) )|||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)|||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|||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!|||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|||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.|||I'd leave the end-date null, like rdjabarov suggested.|||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).|||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.