write query to retrieve records from database

Question

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 11-Dec-12 20:10pm

Harpreet_125

Add a Solution

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

4 solutions

Add a Solution

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

__TR__ · Answer 1 · 2012-12-11T20:42:00

Solution 2

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.

Posted 11-Dec-12 20:42pm

__TR__

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.

choudhary.sumit · Answer 2 · 2012-12-11T20:26:00

Solution 1

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.

Posted 11-Dec-12 20:26pm

choudhary.sumit

Updated 11-Dec-12 20:39pm

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

vasim sajad · Answer 3 · 2012-12-11T20:43:00

Solution 3

SQL

SELECT * from test_table
           WHERE   [DATE] > (SYSDATE-30)
           AND [DATE] < SYSDATE

Posted 11-Dec-12 20:43pm

vasim sajad

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 ?

ashokmmrk · Answer 4 · 2012-12-11T22:47:00

Solution 4

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'

Posted 11-Dec-12 22:47pm

ashokmmrk

write query to retrieve records from database

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

write query to retrieve records from database

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us