Identifying week range by todays date

[werosen1]

I'm trying to write an SQL query that identifies today's date, and then provides the date range for that date's week, so I can return all my database records that fall in that week's date range and display them in my ASP page.

Is there a way to do this?

Regards

 

[snyperx3]

I copied this from one of my databases. You can just change it as needed. [Opened Date] is date variable needed to be replaced.

SELECT *
FROM Issues
WHERE (([Opened Date]) Is Not Null)
GROUP BY Format([Opened Date],'yyyy-ww'), [Opened Date]
HAVING (((Format([Opened Date],'yyyy-ww'))=Format(Date(),'yyyy-ww')));

-Pete

 

[werosen1]

Unfortunately that returns an error as follows:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Cannot group on fields selected with '*'.

/weekinreview.asp, line 118

 

[snyperx3]

Gotcha. then replace the * with whatever fields you need from your table.

-Pete

 

[werosen1]

Here's what I'm using:

SELECT Name,Date FROM reports WHERE ((4/17/2006) Is Not Null) GROUP BY Format(4/17/2006,'yyyy-ww'), 4/17/2006 HAVING (((Format(4/17/2006,'yyyy-ww'))=Format(Date(),'yyyy-ww')));

It returns:

[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'Name' as part of an aggregate function.

 

[snyperx3]

is Date the name of your variable?

if so...no no. You shouldnt use function names as variable names. Anyway, try this if you did name that variable Date:

SELECT Name,Date FROM reports WHERE reports.Name Is Not Null GROUP BY Format(reports.Date,'yyyy-ww'), reports.Date HAVING (((Format(reports.Date,'yyyy-ww'))=Format(Date(),'yyyy-ww')));

-Pete

 

[werosen1]

Sorry, I didn't know that Date was a function. I'll rename the field _Date in the database. Meanwhile, I tried your suggestion and got:

[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'Name' as part of an aggregate function

 

[snyperx3]

sorry...i didnt remove a part that should have been. since there are no aggregate functions we dont need to group.

SELECT * FROM reports WHERE (((Format(reports.Date,'yyyy-ww'))=Format(Date(),'yyyy-ww')));

-Pete

 

[werosen1]

Pete:

That works beautifully. I really appreciate the help.

One more question if I may. Is it more efficient for me to use '*' or to specify only the fields from which I need the data?

Warren

 

[Golom]

Technically, splat ("*") is the least efficient but the differences are usually not very significant.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[snyperx3]

yeah, logically it would have to be less efficient to use *.

-Pete