FINDING THE DATE WHICH IS NEAREST TO CURRENT DATE

Question

Post reply

FINDING THE DATE WHICH IS NEAREST TO CURRENT DATE

aeri_q

Mr or Mrs. 500

Points: 553
More actions
February 2, 2014 at 3:37 pm

#287884

i need to find the date from a column , which is less than and is nearest to the current date .??
any help would be appreciated

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Here's an example... I'm just creating a bunch of dates from a table of numbers.

SELECT TOP 1 SomeDate, N

FROM

(SELECT DATEADD(dd,n,'1-1-2010') AS SomeDate, n

FROM dbo.Tally) x

WHERE SomeDate<=GETDATE();

What you want is probably something like

SELECT TOP 1 SomeDateColumn

FROM MyTable

WHERE SomeDateColumn<=GETDATE();

GETDATE() returns the current date...

HTH

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

pietlinden (2/2/2014)
What you want is probably something like
SELECT TOP 1 SomeDateColumn
FROM MyTable
WHERE SomeDateColumn<=GETDATE();
GETDATE() returns the current date...

I believe that's missing an ORDER BY.

--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)

pietlinden SSC Guru Points: 63503 More actions · Answer 3

sure enough... must be getting old... forgot that my Tally table is indexed, so that caused me to overlook it.

Definitely need an ORDER BY clause! TOP VALUES without an ORDER BY will return perhaps inconsistent results.... Not generally a good idea!

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

pietlinden (2/2/2014)
sure enough... must be getting old... forgot that my Tally table is indexed, so that caused me to overlook it.
Definitely need an ORDER BY clause! TOP VALUES without an ORDER BY will return perhaps inconsistent results.... Not generally a good idea!

I am sure you know this but in case other people stumble across this thread it is important to note that just because the tally is indexed does not mean the results will be in the order of the clustered index.

This is one of those fallacies that many many people believe. The one and only way to ensure the order of a result set is to include an order by.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

barsuk SSChampion Points: 12280 More actions · Answer 5

I am using abs function:

WHERE ABS(DATEDIFF(DAY, @prev_date, @end_date))...

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

barsuk (2/3/2014)
I am using abs function:
WHERE ABS(DATEDIFF(DAY, @prev_date, @end_date))...

Using functions like that in your where clause will render your query nonSARGable. That means you have effectively rendered indexing useless.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

barsuk SSChampion Points: 12280 More actions · Answer 7

barsuk

SSChampion

Points: 12280

February 4, 2014 at 1:16 pm

#1685995

I know. I was using that for non-production data