ASP select all from data range

[techskool]

Hi

Im want to compare records in a database, which have a value dd/mm/yyyy (set to a variable : DateLogged )in one field, in a query for matches from the last 2 weeks additions (a date is genetaed on the date of an addition)

I was thinking : sql="SELECT * FROM stock WHERE DateLogged BETWEEN '%" &date1& "%' AND '%" &date2& "%' AND InStock = 'Yes' ORDER BY Artist, Title, Label"

How do i set the values for date1 and date2 variables (which i want to cover a 2 week range,... am i along the right lines, or will the BETWEEN function not work with date comparisons

Thanks in adavance

Dave

 

[Microbe]

off the top of my head...

You will only get the between function to work if you have the date in yyyymmdd format I think.

Use datadd to get your 2 week spread, either add 14 days to early date or subtract 14 from later.

Or, if you have them in yyyymmdd format, it is straight math.

Steve Davis
hey.you@hahaha.com.au

http://www.hahaha.com.au

Me? I can't even spell ASP!

 

[Veep]

myDate1 = Date()
myDate2 = DateAdd("d",-14,myDate1)

This will give you the current date in variable myDate1 and the date 14 days ago in myDate2. The BETWEEN operator will work fine (but remember it's inclusive).

You could also use:
myDate2 = DateAdd("ww",-2,myDate1)
which is the date two weeks ago.

A better way to do it is to enter myDate1 via a form control ie: myDate1=request.form("txtDate&quot. Then myDate2 will automatically calc back 2 weeks (or 14 days) from whatever myDate1 was.