date Different
-
I have table with :
if today is 25/11/2020, then i need to retrive ID 5 because nearst date to today WeekDate is: 1/12/2020
then when today date is: 10/12/2020 script must retrieve ID 6
i tried the below but it is not working, please need help
DATEDIFF(d, WeekDate, CONVERT(datetime, @CurrentDate, 103)) >= 0
-
Is this the next date, meaning today is <= to the date in the table?
What I'd do is set up a number of scenarios to query, so you can test them all. Then run your query against lots of parameter dates.
- ikrami2000 wrote:
I have table with :
if today is 25/11/2020, then i need to retrive ID 5 because nearst date to today WeekDate is: 1/12/2020
then when today date is: 10/12/2020 script must retrieve ID 6
i tried the below but it is not working, please need help
DATEDIFF(d, WeekDate, CONVERT(datetime, @CurrentDate, 103)) >= 0
ikrami2000 wrote:I have table with :
if today is 25/11/2020, then i need to retrive ID 5 because nearst date to today WeekDate is: 1/12/2020
then when today date is: 10/12/2020 script must retrieve ID 6
i tried the below but it is not working, please need help
DATEDIFF(d, WeekDate, CONVERT(datetime, @CurrentDate, 103)) >= 0
Your request makes no sense to me. Because of the leading "25" in one of your date examples, I can only assume the dates you posted are in the DD/MM/YYYY format. That means that both the 25/11/2020 (25 Nov 2020) and the 10/12/2020 (10 Dec 2020) dates are temporally later than the lasted date of 12/10/2020 (12 Oct 2020) and both requested dates should return the data from ID 6 if you're trying to get the "closest date" in any fashion.
Please rework your example so that it makes sense so we can help. Please see how to post "Readily Consumable Data" by reading/heeding the first link in my signature line below to help us help you more quickly with a tested coded answer.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You are correct date format dd/mm/yyyy
my question is :
When Today date is 25/11/2020
need to get nearest date in the below table which is (01/12/2020) (ID = 5)
if today date is 09/12/2020 so i will get (10/12/2020) (ID = 6)
and so on
-
This is my query and working fine if today date and table date in same month:
Declare @CurrentDate nvarchar (20);
Set @CurrentDate = '25/11/2020'
SELECT Top(1) ID FROM Weeks
WHERE
WeekName <> 'ALL'
AND
DATEDIFF(day, WeekDate, CONVERT(datetime, @CurrentDate, 103)) >= 0 order by WeekDate DESC
-
This query would perform much better, considering there is an index on WeekDate:
SELECT Top(1) ID FROM Weeks
WHERE
WeekName <> 'ALL'
AND
WeekDate, >= CONVERT(datetime, @CurrentDate, 103))
order by WeekDate DESC
_____________
Code for TallyGenerator -
Somebody didn't read the article I directed them to. No problem. I'll just post the code in the same manner that the example data was posted to ensure they're equally readily consumable. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
--DECLARE @CurrentDate as date='2020-11-25'
DECLARE @CurrentDate as date='2020-12-10'
;WITH Data (Id, WeekName, WeekDate) AS (
SELECT 5, 'Gameweek 1', CONVERT(date,'2020-12-01')
UNION
SELECT 6, 'Gameweek 2', '2020-12-10'
)
SELECT TOP(1) *
FROM Data d
ORDER BY ABS(DATEDIFF(dd,@CurrentDate,d.WeekDate)) - Jonathan AC Roberts wrote:
--DECLARE @CurrentDate as date='2020-11-25'
DECLARE @CurrentDate as date='2020-12-10'
;WITH Data (Id, WeekName, WeekDate) AS (
SELECT 5, 'Gameweek 1', CONVERT(date,'2020-12-01')
UNION
SELECT 6, 'Gameweek 2', '2020-12-10'
)
SELECT TOP(1) *
FROM Data d
ORDER BY ABS(DATEDIFF(dd,@CurrentDate,d.WeekDate))Jonathan... Try that on the following more realistically sized table and see why I don't recommend that method.
DROP TABLE IF EXISTS Data
;
GO
SELECT ID = t.N
,WeekName = CONCAT('Gameweek',t.N)
,Weekdate = ISNULL(DATEADD(dd,t.N*7,'2000'),0) --ISNULL makes the column NOT NULL
INTO Data
FROM dbo.fnTally(0,1200) t
;
ALTER TABLE Data
ADD PRIMARY KEY CLUSTERED (Weekdate)
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank Jeff, you managed to stop on the triple dots sign. Good on you. I'm not sure I could do it so nicely. 😉
but I'm still not sure the OP did not actually mean "nearest future date". The examples provided don't make it clear, and the common sense suggest that nobody would be interested in the "nearest game date" which is in the past.
_____________
Code for TallyGenerator - Jeff Moden wrote:Jonathan AC Roberts wrote:
--DECLARE @CurrentDate as date='2020-11-25'
DECLARE @CurrentDate as date='2020-12-10'
;WITH Data (Id, WeekName, WeekDate) AS (
SELECT 5, 'Gameweek 1', CONVERT(date,'2020-12-01')
UNION
SELECT 6, 'Gameweek 2', '2020-12-10'
)
SELECT TOP(1) *
FROM Data d
ORDER BY ABS(DATEDIFF(dd,@CurrentDate,d.WeekDate))Jonathan... Try that on the following more realistically sized table and see why I don't recommend that method.
DROP TABLE IF EXISTS Data
;
GO
SELECT ID = t.N
,WeekName = CONCAT('Gameweek',t.N)
,Weekdate = ISNULL(DATEADD(dd,t.N*7,'2000'),0) --ISNULL makes the column NOT NULL
INTO Data
FROM dbo.fnTally(0,1200) t
;
ALTER TABLE Data
ADD PRIMARY KEY CLUSTERED (Weekdate)
;Yes, I see what you mean it would have to scan the entire table.
Sergiy wrote:Thank Jeff, you managed to stop on the triple dots sign. Good on you. I'm not sure I could do it so nicely. 😉
Not sure what you mean, the only place I could see a triple dots sign is after "Jonathan..." So what do you mean?
-
Declare @CurrentDate Date = '10/12/2020';
Select Top 1 @CurrentDate,*
From (
Select Top 1 w.*, DaysDiff = DATEDIFF(dd,@CurrentDate,w.WeekDate)
From Weeks w
Where w.WeekDate >= @CurrentDate
Union All
Select Top 1 w.*, DaysDiff = DateDiff(dd, w.WeekDate, @CurrentDate)
From Weeks w
Where w.WeekDate <= @CurrentDate
) dif
Order By dif.DaysDiff ;
if @CurrentDate Date = '11/12/2020';
will return weekdate 12/1/2020
also i tried to get 1/12/2020 until today is 10/12/2020
but failed
-
Just a suggestion, when dealing with dates it would be best if you used the ISO standard YYYYMMDD and not your regional standard as that can be ambiguous. When you see 10/12/2020, is that 10 December 2020 or 12 October 2020? If you see 20201210 it is clear that you are talking about 10 December 2020.
Also, to clarify, is your requirement to find the nearest future date to todays date including if that date is today?
-
Thank you for all your Help, I did it like the below:
set @ChangedDate = (Select DATEADD(month, 1, CONVERT(datetime, @CurrentDate, 103)))
Set @ChangedDate = (SELECT DATEADD(DAY,1,EOMONTH(CONVERT(datetime, @ChangedDate, 103),-1)))
Set @ChangedDate = FORMAT( CONVERT(datetime, @ChangedDate, 103), 'd', 'en-US' )
Set @WeekID =
Case
When
(SELECT Top(1) ID FROM Weeks
WHERE
WeekName <> 'ALL'
AND
DATEDIFF(day, WeekDate, CONVERT(datetime, @CurrentDate, 103)) >= 0 order by WeekDate ASC
) is null
Then
(SELECT Top(1) ID FROM Weeks
WHERE
WeekName <> 'ALL'
AND
DATEDIFF(day, WeekDate, CONVERT(datetime, @ChangedDate, 103)) >= 0 order by WeekDate ASC
)
else
(SELECT Top(1) ID FROM Weeks
WHERE
WeekName <> 'ALL'
AND
DATEDIFF(day, WeekDate, CONVERT(datetime, @CurrentDate, 103)) >= 0 order by WeekDate DESC
)
end -
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply