How do I search between 2 dates?

ASP Free Forums Sponsor:

Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

March 28th, 2008, 06:19 AM

dfaz

Contributing User

Join Date: Jul 2007

Posts: 56

Time spent in forums: 10 h 11 m 55 sec
Reputation Power: 1

Query - General - How do I search between 2 dates?

Hi guys,

Hopefully someone knows th answer to this, i've done a bit of searching but haven't quite found what i'm looking for. I want to be able to run a query that searches a table (tblservice) and displays all the records in the last week.

But i'm not sure how to compare between todays date and last weeks date. i have a field in my table with the date that gets entered im the format d/mm/yy/

Any ideas? Thanks!

Dean

March 28th, 2008, 08:24 AM

Wolffy

Slaprentice of Wolves

Join Date: Aug 2007

Location: Mossville, IL

Posts: 1,015

Time spent in forums: 1 Week 4 Days 2 h 3 m 50 sec
Reputation Power: 329

You don't say what db engine you have -- many of the Date/Time functions are language dependent. Also, I hope your date column is a DateTime format or your in for a world of hurt.

In Oracle, it would be
SELECT * FROM table WHERE aDate BETWEEN TRUNC(SYSDATE - 7) AND TRUNC(SYSDATE)

__________________
Wolffy
------------------------
Teaching people to fish.

March 28th, 2008, 01:31 PM

dfaz

Contributing User

Join Date: Jul 2007

Posts: 56

Time spent in forums: 10 h 11 m 55 sec
Reputation Power: 1

I'm using an access database and yes i have it in a date/time format

March 28th, 2008, 02:04 PM

Wolffy

Slaprentice of Wolves

Join Date: Aug 2007

Location: Mossville, IL

Posts: 1,015

Time spent in forums: 1 Week 4 Days 2 h 3 m 50 sec
Reputation Power: 329

Quote:

Originally Posted by dfaz

I'm using an access database and yes i have it in a date/time format

SELECT * FROM table WHERE aDate BETWEEN DateAdd("ww", -1 Date()) AND Date()

April 14th, 2008, 10:09 AM

dfaz

Contributing User

Join Date: Jul 2007

Posts: 56

Time spent in forums: 10 h 11 m 55 sec
Reputation Power: 1

Thanks for getting back to me Wolfy, i put the code in a dn it cam up with an error that said "End of statement expected"

Also in the SQL string is aDate supposed be a variable set by me? I have simply copied the coe you gave and changed my tablename.

Another thing worth noting is that i have noticed that access changes my data around, i put this code in to create the date:

Day(Now) & "/" & month(Now) & "/" & year(Now)
i.e. 14/4/2008

and the access field is set up for short date, when i recall a record the date would be 4/14/2008.

any ideas with the sql string?

thanks

Dfaz

April 14th, 2008, 10:22 AM

Wolffy

Slaprentice of Wolves

Join Date: Aug 2007

Location: Mossville, IL

Posts: 1,015

Time spent in forums: 1 Week 4 Days 2 h 3 m 50 sec
Reputation Power: 329

End of statement expected -- either a missing paren or quote. Since you didn't post yoru code, I can't tell.
aDate is a column in your table.
Why are you doing all that work to store a date:
INSERT INTO table (aDate) VALUE(Date())
Dates are stored as a number of ticks since zero-hour. Access will display the date based on the locale of you computer (I believe...I'm not an Access guru).

April 19th, 2008, 02:00 PM

dfaz

Contributing User

Join Date: Jul 2007

Posts: 56

Time spent in forums: 10 h 11 m 55 sec
Reputation Power: 1

Quote:

Originally Posted by Wolffy

Why are you doing all that work to store a date:
INSERT INTO table (aDate) VALUE(Date())

Not sure really, the record is created and saved on a different page and i wanted the date to be displayed to the user on the page when they opened the page. When saving the record i then just used the value of the text box the date was saved in. I'll change my code.

Anyway the code i'm using is as follows:

Code:

'Create  connection string
    Dim connString As New String(ConfigurationSettings.AppSettings("ConnString"))
				 		
		
    'Open a connection
     objConnection = New OleDbConnection(ConnString)
     objConnection.Open()
    
    'Specify the SQL string
	StrSQL = "SELECT * FROM tblrequests WHERE RDate BETWEEN DateAdd("ww", -1 Date()) AND Date()"
	
	adapter = new OleDbDataAdapter( StrSQL, connstring )

		ds = new DataSet				
		adapter.Fill (ds)
		
dg.DataSource = ds ' assign the dataset to the datagrid

dg.DataBind() 'bind the data to the datagrid

End Sub

Rdate being the name of my field in the table

thanks for all the continued help

Dfaz

April 21st, 2008, 01:45 AM

micky

Couch Potato Wizard

Join Date: Jan 2005

Location: India

Posts: 9,962

Folding Points: 1480 Folding Title: Novice Folder

Time spent in forums: 3 Months 2 Weeks 4 Days 13 h 54 m 16 sec
Reputation Power: 1275

i think u r missing a comma in ur sql, try this

Code:

StrSQL = "SELECT * FROM tblrequests WHERE RDate BETWEEN DateAdd("ww", -1, Date()) AND Date()"

Comments on this post

Wolffy agrees: Yup, looks like a dropped a comma back in my example.

__________________
Laziness is my religion and Sunday is my God

Get the Mantra!

April 22nd, 2008, 03:00 PM

dfaz

Contributing User

Join Date: Jul 2007

Posts: 56

Time spent in forums: 10 h 11 m 55 sec
Reputation Power: 1

Ok, i have changed the SQl as advised and the error "BC30205: End of statement expected." still came up. Code as follows, any other ideas

Code:


'Create  connection string
    Dim connString As New String(ConfigurationSettings.AppSettings("ConnString"))
				 		
		
    'Open a connection
     objConnection = New OleDbConnection(ConnString)
     objConnection.Open()
    
    'Specify the SQL string
	StrSQL = "SELECT * FROM tblrequests WHERE RDate BETWEEN DateAdd("ww", -1, Date()) AND Date()"
	
	adapter = new OleDbDataAdapter( StrSQL, connstring )

		ds = new DataSet				
		adapter.Fill (ds)
		
dg.DataSource = ds ' assign the dataset to the datagrid

dg.DataBind() 'bind the data to the datagrid

End Sub

#10

April 22nd, 2008, 03:13 PM

Wolffy

Slaprentice of Wolves

Join Date: Aug 2007

Location: Mossville, IL

Posts: 1,015

Time spent in forums: 1 Week 4 Days 2 h 3 m 50 sec
Reputation Power: 329

It's the double quotes around the ww -- change 'em to single quotes. Yup, I had double quotes in my example.

Comments on this post

dfaz agrees!

#11

April 22nd, 2008, 03:18 PM

dfaz

Contributing User

Join Date: Jul 2007

Posts: 56

Time spent in forums: 10 h 11 m 55 sec
Reputation Power: 1

I did wonder about that, but was not sure so didn't mention it. Anyway my code is working fine now,

Wolffy and Micky thank you very much for your help.

Dfaz

#12

April 22nd, 2008, 03:20 PM

Wolffy

Slaprentice of Wolves

Join Date: Aug 2007

Location: Mossville, IL

Posts: 1,015

Time spent in forums: 1 Week 4 Days 2 h 3 m 50 sec
Reputation Power: 329

Glad you got it sorted -- sorry I double-duffed on my example.

#13

April 24th, 2008, 02:48 AM

micky

Couch Potato Wizard

Join Date: Jan 2005

Location: India

Posts: 9,962

Time spent in forums: 3 Months 2 Weeks 4 Days 13 h 54 m 16 sec
Reputation Power: 1275

glad i cud help

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: