I have used an existing vb process to supply dates, but I am switching
over to a pure SQL. Below is the previous logic.

Select Case Weekday(Date, vbSunday)
Case Is = 1
' We are at a Sunday so we need to go
' to previous Thursday
txtStartDate = DateAdd("d", -3, Date)
Case Is = 2
' We are at a Monday so we need to go
' to previous Thursday
txtStartDate = DateAdd("d", -4, Date)
Case Is = 3
' We are at a Tuesday so we need to go
' to previous Thursday
txtStartDate = DateAdd("d", -5, Date)
Case Is = 4
' We are at a Wednesday so we need to go
' to previous Tuesday
txtStartDate = DateAdd("d", -1, Date)
Case Is = 5
' We are at a Thursday so we need to go
' to previous Tuesday
txtStartDate = DateAdd("d", -2, Date)
Case Is = 6
' We are at a Friday so we need to go
' to previous Tuesday
txtStartDate = DateAdd("d", -3, Date)
Case Is = 7
' We are at a Saturday so we need to go
' to previous Thursday
txtStartDate = DateAdd("d", -2, Date)
Case Else
txtStartDate = Date
End Select

As you can see when current date = Saturday - Tuesday, the query date
needs to be the previous Thursday, Otherwise, the previous Tuesday.

For today (27/12/2005) the date would be (22/12/2005).

Thanks for any help!
CT

"ct witter" <norcold1@yahoo.com> wrote in message
news:1135693029.560777.163890@g14g2000cwa.googlegr oups.com...
>I have used an existing vb process to supply dates, but I am switching
> over to a pure SQL. Below is the previous logic.
>
> Select Case Weekday(Date, vbSunday)
> Case Is = 1
> ' We are at a Sunday so we need to go
> ' to previous Thursday
> txtStartDate = DateAdd("d", -3, Date)
> Case Is = 2
> ' We are at a Monday so we need to go
> ' to previous Thursday
> txtStartDate = DateAdd("d", -4, Date)
> Case Is = 3
> ' We are at a Tuesday so we need to go
> ' to previous Thursday
> txtStartDate = DateAdd("d", -5, Date)
> Case Is = 4
> ' We are at a Wednesday so we need to go
> ' to previous Tuesday
> txtStartDate = DateAdd("d", -1, Date)
> Case Is = 5
> ' We are at a Thursday so we need to go
> ' to previous Tuesday
> txtStartDate = DateAdd("d", -2, Date)
> Case Is = 6
> ' We are at a Friday so we need to go
> ' to previous Tuesday
> txtStartDate = DateAdd("d", -3, Date)
> Case Is = 7
> ' We are at a Saturday so we need to go
> ' to previous Thursday
> txtStartDate = DateAdd("d", -2, Date)
> Case Else
> txtStartDate = Date
> End Select
>
> As you can see when current date = Saturday - Tuesday, the query date
> needs to be the previous Thursday, Otherwise, the previous Tuesday.
>
> For today (27/12/2005) the date would be (22/12/2005).
>
> Thanks for any help!
> CT
>

http://tahiti.oracle.com -- download SQL reference manual

SQL supports CASE

the to_char() function can extract the day of week from a date datatype

date arithmetic on oracle date datatypes is in units of days (you won't need
a DateAdd function, just your plus/minus)

the SYSDATE function returns the current date

++ mcs