previous week 1

[phpatrick]

WEEK: DatePart("ww";[DAG];2;1)

I need a query where he select the values of the previous week, how to do this.
This is weeknumer 2, so I need the records of previous week, this was weeknumber 1
This can't be hardcoded, because every week you got a new previous week

 

[Golom]

Weeks are from Sunday to Saturday (at least by default) so Sunday of the this week is

Dateadd("d",-Weekday(Date)+1,Date)

and Sunday of the preceding week is

Dateadd("d",-Weekday(Date)+1,Date) - 7

Finally, the records for the previous week are

WHERE DateField BETWEEN Dateadd("d",-Weekday(Date)+1,Date) - 7 AND
                        Dateadd("d",-Weekday(Date)+1,Date) - 1

 

[phpatrick]

I still have some littel trouble. We use european weeks, these start from monday until sunday.
What do I adapt on the code ?

Between DateAdd('d';-Weekday([DAG]);Date())-7 And DateAdd('d';-Weekday([DAG]);Date()-1)

 

[phpatrick]

I used this, but don't know if he takes the weekend also (no data for the weekend)

Between DateAdd('ww';-1;Date()) And DateAdd('ww';-2;Date())

 

[Golom]

WHERE DateField BETWEEN 
      Dateadd("d",-Weekday(Date,[red]2[/red])+1,Date) - 7 AND
      Dateadd("d",-Weekday(Date,[red]2[/red])+1,Date) - 1

I notice that you have substituted the field [DAG] into the "WeekDay" call but have left "Date()" elsewhere. That probably won't give you the result you are looking for. Do you want something like

WHERE [red][DAG][/red] BETWEEN 
      Dateadd("d",-Weekday(Date,[red]2[/red])+1,Date) - 7 AND
      Dateadd("d",-Weekday(Date,[red]2[/red])+1,Date) - 1

?

 

[phpatrick]

He does not regcognize Date in my sql instruction, shoouldn't that be a function ? date()

 

[Golom]

Yes it should. SQL usually recognizes "Date" and "Date()" to be the same thing but you may need to use "Date()" to be explicit about it.

 

[phpatrick]

ok, now it works thanks.

 

[PHV]

SQL usually recognizes "Date" and "Date()" to be the same
For me, JetSQL NEVER recogizes that ...