Click here to Skip to main content
15,919,341 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have to write query to retrieve that records from database which have whatever previous date exists from todays date..

like today is 12/12/2012. and this query will check for previous date (12/11/2012). if it exists then fetch records... if it does not exist then it will check for (12/10/2012) and so on...

hope you understand my question..
Posted
Comments
Kiran Susarla 12-Dec-12 2:23am    
can you please post whatever you have tried till now?
Harpreet_125 12-Dec-12 2:36am    
i have tried this query... "select * from test_table where [DATE] < "'+System.DateTime.Now.ToShortDateString()+'"".. but it is fetching all the previous date records..
__TR__ 12-Dec-12 2:26am    
Is your date format MM/dd/yyyy or dd/MM/yyyy ?
Harpreet_125 12-Dec-12 2:32am    
yes..
__TR__ 12-Dec-12 2:35am    
Not clear on which date format are you using. Please specify the date format.

Try
SQL
SELECT * FROM YourTable
WHERE DateCol =
(
	SELECT MAX(DateCol) AS DateCol FROM YourTable
	WHERE DATEDIFF(DAY,GETDATE(),DateCol) < 0
)


Let me know if you have any questions.
 
Share this answer
 
Comments
Harpreet_125 12-Dec-12 2:59am    
actually what i want is when query found previous date from today'd date whatever it is.. suppose it is 12/19/2012.. then query should not fetch records of other than this date.. hope you all understand my question.. i think your querys will fetch all the previous date records.. i want only single date records...
__TR__ 12-Dec-12 3:07am    
12/19/2012 is greater than today's date. What exactly is your requirement?
Harpreet_125 12-Dec-12 3:05am    
sir your query is working fine.. but i dont understand your query.. can u explain this..
__TR__ 12-Dec-12 3:10am    
The query I posted takes the latest date that is less than today's date retrieves the records for that date.
try this query :
C#
TimeSpan ts= new TimeSpan(1,0,0,0)
            
"select * from test_table where [DATE] < "'+System.DateTime.Now.ToShortDateString()+'" and [DATE] > "'+System.DateTime.Now.Subtract(ts).ToShortDateString()+'""

now you can subtract day values as much you want.
 
Share this answer
 
v2
Comments
Harpreet_125 12-Dec-12 2:32am    
but i think it will retrieve all the previous date records. i dont want that... i have to only fetch single date records that is date before todays date or so on..
choudhary.sumit 12-Dec-12 2:40am    
answer updated! you can also use between keyword instead of using < and >
Jibesh 12-Dec-12 2:39am    
instead of < try between AND combination in your where query
SQL
SELECT * from test_table
           WHERE   [DATE] > (SYSDATE-30)
           AND [DATE] < SYSDATE
 
Share this answer
 
Comments
Harpreet_125 12-Dec-12 2:59am    
actually what i want is when query found previous date from today'd date whatever it is.. suppose it is 12/19/2012.. then query should not fetch records of other than this date.. hope you all understand my question.. i think your querys will fetch all the previous date records.. i want only single date records...
vasim sajad 12-Dec-12 3:13am    
wer u want filter with ur condition ? i mean code or sp ?
vasim sajad 12-Dec-12 3:15am    
I didn't get u fully. i hope ur requirement is to fetch last one record ? right ?
Hi,

Try this

select * from test_table where DATE between DATEADD(DAY,-1,'2012-12-12 00:00:00.000') and '2012-12-12 00:00:00.000'
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900