select date closest to today
-
hi all,
I need some help with a stored proc to select the date in table that is closest to today's date.
The Table:
field: HiD (primary, not null, int ident)
field: HDate (datetime)
So its something like:
HiD HDate
1 2/15/2004
2 4/10/2004
3 1/29/2002
How do i find the HiD whose date is closest to today?
Thank you for any help and advice.
-
How to select in the past (including today)
SELECT HiD
FROM TheTable
WHERE HDate = (
SELECT MAX(HDate)
FROM TheTable
WHERE HDate < GETDATE()
)
How to select in the past (not including today)
SELECT HiD
FROM TheTable
WHERE HDate = (
SELECT MAX(HDate)
FROM TheTable
WHERE HDate < DATEADD(dd, -1, GETDATE())
)
N 56°04'39.16"
E 12°55'05.25" -
Peter, thank you very much for your help.
What if the date field includes possible future dates?
Thanks again.
-
select
top 1 Hid, HDate
from
TheTableorder
by abs(HDate - getdate()) asc, Hid descTim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
This gave an error when I tried it
My solution
SELECT TOP 1 Hid, HDate
FROM
ORDER BY ABS(DATEDIFF(day,HDate,GETDATE())) ASC
This will ignore time portion of any dates and will give you arbitary row if there are more than 1 row with equidistant (ie before and after today) difference
use
, DATEDIFF(day,HDate,GETDATE()) DESC
for before today
or
--, DATEDIFF(day,GETDATE(),HDate) DESC
for after today
other than that you can use Tim's idea and use Hid (ASC or DESC)
Far away is close at hand in the images of elsewhere.
Anon. -
Doesn't matter, since the date picked is the latest date to the date given.
How to select in the past (including today)
DECLARE @WantedDate DATETIME
SELECT @WantedDate = 'June 19, 2006'
SELECT HiD
FROM TheTable
WHERE HDate = (
SELECT MAX(HDate)
FROM TheTable
WHERE HDate < @WantedDate
)
How to select in the past (not including today)
SELECT @WantedDate = GETDATE()
SELECT HiD
FROM TheTable
WHERE HDate = (
SELECT MAX(HDate)
FROM TheTable
WHERE HDate < DATEADD(dd, -1, @WantedDate)
N 56°04'39.16"
E 12°55'05.25" -
Implicit conversion from data type datetime to float is not allowed. Use the CONVERT function to run this query.
I would use something like decimal(20,10):
- select
top 1 Hid, HDate
- from
TheTable
- order
by abs(cast(HDate - getdate() as decimal(20,10))) asc, Hid desc
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Thank you all for your help and guidance.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply