Link to home
Start Free TrialLog in
Avatar of pjordanna
pjordanna

asked on

SQL SELECT STATEMENT QUESTION - DATE BASED

Hi Experts,

Happy new year!


I have the following table:


country          location    bkgDateStart                                  bkgDateEnd                       pickUpStart                        pickUpEnd                         code          band         supplier

ESP           IBZ          2004-12-01 00:00:00              2005-12-31 00:00:00      2005-01-05 00:00:00              2005-01-07 00:00:00      NULL      C      ACH1
ESP           IBZ          2005-01-01 00:00:00              2005-01-10 00:00:00      2005-01-01 00:00:00              2006-01-01 00:00:00      NULL      F      ACH1
ESP           IBZ          2004-12-01 00:00:00              2005-12-31 00:00:00      2005-01-08 00:00:00              2005-01-13 00:00:00      NULL      C      ACH1



I am trying to write a SQL SELECT statement which will return rows based on "bkgDateStart" and "bkgDateEnd" using todays date. Basically if todays date is between "01/01/2005" and "10/01/2005" then I need the SELECT staement to return only row 2. If not then I want to return rows 1 and 3. The idea is that I am looking for the rows which best fit the date range between "bkgDateStart" and "bkgDateEnd". As standard these will be set to "01/12/2004" and "31/12/2005" respectively but I need to be able to put rows in the table which have date ranges that sit inside of those 2 dates so that I can pull out only the relevant row at the relevant time.


Any ideas?



PJORDANNA


Avatar of SjoerdVerweij
SjoerdVerweij

What if a row is added with the date range 05-01-02 - 05-01-09, current date being 05-01-04? Do you want both, or just the "best"?
Avatar of pjordanna

ASKER

Hi SjoerdVerweij,

No not both - just the best.



PJORDANNA
ASKER CERTIFIED SOLUTION
Avatar of Hilaire
Hilaire
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Vitor Montalvão
SELECT *
FROM MyTable
WHERE GETDATE() BETWEEN bkgDateStart  AND bkgDateEnd

Now, how can I know what's the best record?
Hilaire's should work -- but I don't know if you can substract datetimes like that. It might have to be

select TOP 1 *
from <YourTable>
where getdate() between bkgDateStart and bkgDateEnd
order by abs(datediff(day, bkgDateEnd, bkgDateStart))
>>I don't know if you can substract datetimes like that<<
Yes you can
Hilaire,

Thanks for that. Works a treat!




PJORDANNA