• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

SQL query to assign the nearest previous date that is there in the database for an input field

Hi,

I have to write a Sql Query like
if the input date is not there in the database then assign the date nearest previous date
that is there in the database.

Ex: I have dates  like 12/31/2009,  1/31/2010 in the database
  if my input date is 1/21/2010, then i need to check if that date is there in the table
 if it is not there then get the nearest previous date i,e 12/31/2009 and assing to the input
date field.

Please help me on this.

Thanks
0
ram27
Asked:
ram27
  • 3
  • 3
  • 3
  • +2
2 Solutions
 
sdstuberCommented:
select top 1 yourdate from yourtable
where yourdate <= inputdate
order by yourdate
0
 
Ephraim WangoyaCommented:

DECLARE @DATE DATETIME
SET @DATE =  '1/21/2010'

IF NOT EXISTS(SELECT 1 FROM MYTABLE WHERE MYDATE = @DATE)
  SELECT @DATE = MYDATE FROM MYTABLE WHERE MYDATE  < @DATE ORDER BY MYDATE DESC
0
 
sdstuberCommented:
no need to do an existence check

if you do select top 1 from table where the table date <= input date

then, if the date exists, it'll be returned because it's = input date
if it doesn't, then the most recent will be returned because it's < input date
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Ephraim WangoyaCommented:

Thats correct
Noted
0
 
SharathData EngineerCommented:
sdstuber - you are right. But you need "ORDER BY InputDate DESC" to get the nearest date.
Check this out with and without ORDER BY and see the difference.
declare @date datetime
declare @table table(Inputdate datetime)
insert @table values ('12/31/2009')
insert @table values ('1/31/2010')
insert @table values ('1/11/2010')
set @date = '1/21/2010'
select top 1 InputDate
  from @table
 where Inputdate <= @date
 order by Inputdate desc

Open in new window

0
 
sdstuberCommented:
Sharath_123,

well, in my example it would be "yourdate"  not "inputdate"

yourdate is from yourtable

inputdate is from the user input

sorry for the confusion in my pseudocode, I should have said

<= @inputdate

but, you're right, the order by should be descending


select top 1 yourdate from yourtable
where yourdate <= @inputdate
order by yourdate desc
0
 
HainKurtSr. System AnalystCommented:
I say, get the difference, use abs, order by ascending, get the top one

0
 
HainKurtSr. System AnalystCommented:
here :

select top 1 *
from myTable
order by abs(cast(date_col as int) - cast(@date_input as int))
0
 
HainKurtSr. System AnalystCommented:
my solution gives NEAREST date

so, if you have 1/1/2011 & 15/1/2011 and
input date is 4/1/2011 it will give you 1/1/2011
input date is 12/1/2011 it will give you 15/1/2011

is this what you want? if not, you can remove abs and add a where condition like

select top 1 *
from myTable
where date_col >= @date_input
order by date_col
0
 
Ephraim WangoyaCommented:

The question is looking for previous nearest date
0
 
ram27Author Commented:


 Thanks for all your quick responses

 HainKurt,  i need the nearest previous date only, not the after date..
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now