Re: Supply Date based on DOW

"ct witter" <norcold1_at_yahoo.com> wrote in message news:1135693029.560777.163890_at_g14g2000cwa.googlegroups.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 Received on Tue Dec 27 2005 - 08:36:09 CST