Click here to Skip to main content
15,300,338 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.
Harpreet_125 12-Dec-12 2:37am
   
mm/dd/yyyy
[no name] 12-Dec-12 2:27am
   
You can simply write the select query for fetching the records and in the where clause you can mention your condition like "select * from TableName where [date] < getdate()".
kk2014 12-Dec-12 2:32am
   
what have you done so far? please paste your query here.

which date format are you using from dd/mm/yyyy or mm/dd/yyyy?
Harpreet_125 12-Dec-12 2:40am
   
"select * from test_table where [DATE] < "'+System.DateTime.Now.ToShortDateString()+'""

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

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.
   
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.
   
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
   
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'
   

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