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
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
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"?
ASKER
Hi SjoerdVerweij,
No not both - just the best.
PJORDANNA
No not both - just the best.
PJORDANNA
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT *
FROM MyTable
WHERE GETDATE() BETWEEN bkgDateStart AND bkgDateEnd
Now, how can I know what's the best record?
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))
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
Yes you can
ASKER
Hilaire,
Thanks for that. Works a treat!
PJORDANNA
Thanks for that. Works a treat!
PJORDANNA