CODEWITHSUNDEEP
sqlsql-servert-sqldatetimedate-comparison
Ronn
Ronn

Reputation: 1783

sql date comparison

I was given a query similar to this

select * 
from stuff where stuff.id = 1 
and start_Dt < = todays_date 
and End_Dt > = todays_date

I asked the person who gave it to me why the date comparison, the answer was "The start and end dates are necessary to ensure a unique record match"

I'm confused, wouldn't that comparison equate to any date possible?

Upvotes: 0

Views: 464

Answers (2)

sgmoore
sgmoore

Reputation: 16077

To give an example to illustrate daniel-hilgarth's answer, you may have a table containing a rate which changes overtime.

This particular example shows the standard VAT rate in the UK.

id  StartEffectiveDate  EndEffectiveDate    Rate
1   01/01/1900 00:00    30/11/2008 23:59    17.5
1   01/12/2008 00:00    31/12/2009 23:59    15.0
1   01/01/2010 00:00    03/01/2011 23:59    17.5
1   04/01/2011 00:00    01/01/2099 00:00    20.0

So for todays rate this will return this row

id  StartEffectiveDate  EndEffectiveDate    Rate
1   04/01/2011 00:00    01/01/2099 00:00    20.0

The advantage of this system is that you can implement this table beforehand. So in Dec 2009, it will use 15.0 Vat, but automatically changes to 17.5 once your computerdate reaches 1st Jan 2010 and then change again once the date reaches 4th Jan 2011.

You can also use it to plan future price increases.

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174457

The reason is, that the IDs in your table are not unique, i.e. you can have more than one row with stuff.id = 1.
But only one at any given time is active. This is checked with the date comparison: It returns the row with stuff.id = 1 that is currently valid. This is the row where the start date is in the past (start_Dt <= todays_date) and the end date is in the future (End_Dt >= todays_date).

Upvotes: 3

Related Questions