Last sunday of week

Question

Hi

I'm trying to write a sql query that will return the last sunday of the week for any given date.  Is there an equivalent function to dateadd / datepart in vfp that can help with this?

 

 

 

 

Answer 1

djloa wrote:

I'm trying to write a sql query that will return the last sunday of the week for any given date.  Is there an equivalent function to dateadd / datepart in vfp that can help with this?

 

You can do dimple date arithmetic in VFP:

 

dTomorrow = DATE() + 1

dNextWeek = DATE() + 7

 

You can also find out which day of the week the specified date is by using the DOW() function. Check out DOW() and DAY() functions in the VFP help file.

Answer 2

last Sunday of week?

you can add number of days to any date directly by using "+" operator
e.g.
?date() + 2 && will add two days to the current date

if you want to add month to date then
?gomonth(date(),1) && willl add one month to the current date

if you have datetime field then you can get date part as below:
?ttod(datetime()) && will return you date from the current datetime

Answer 3

What means last Sunday of week ? Week begins with Sunday or Monday ? You mean first Sunday before given date or first Sunday after given date ?

Answer 4

 

I'm after the first Sunday after given date.  So for today, it should return 27 July 2008. 

 

The code I use for SQL server is:

 

DECLARE @Date AS DateTime
set DATEFIRST 1
select DATEADD(dd, 7 - DATEPART(dw, "AS"."DATE"), "AS"."DATE") as ' Wk Ending'

 

 

Its a date rather than a datetime field

 

 

Answer 5

You may try with:

 

NextSunday = (8 - DOW(DATE())) + DATE()

Answer 6

There are similar and maybe better functions in VFP but I don't see how would that help you to find last sunday of week for any given date (actually I didn't iunderstand what last sunday of a week is - last sunday of a given month you mean?). You can use usedr defined functions in your SQL. ie:

 

select myDate, GetNthDay( month(myDate), 5, year(myDate), 1 ) as lastSunday from myTable

 

Procedure GetNthDay

Lparameters tnMonth, tnWeek, tnYear, tnDOW

Local ldFDOM,ldFirstDay,ldDate

ldFDOM = Date(m.tnYear,m.tnMonth, 1)

ldFirstDay = m.ldFDOM-Dow(m.ldFDOM,m.tnDOW)+8

ldDate = m.ldFirstDay+(m.tnWeek-1)*7

Return Iif(Month(m.ldDate)=m.tnMonth,m.ldDate,m.ldDate-7)

endproc

 

 

Answer 7

This works except for if the day of week is a Sunday.  I suppose I need a case type statement to say if dow is sunday then return DATE else (8 - DOW(DATE())) + DATE().  Not knowing VFP that well, how do I do this?

 

Cheers

Answer 8

Try this one - it should do what you want

 

Code Snippet

**************************************************************
*** Returns date of next Day Number.
*** 1 = Sunday, 2 = Monday etc
**************************************************************
FUNCTION NextDate( tnDay, tdInDate )
LOCAL lnDayDum, ldInDate, ldRetVal

*** Must get a day number here
IF VARTYPE( tnDay ) # "N" OR EMPTY( tnDay )
  ASSERT .F. MESSAGE "Must Pass a Day Number"
  RETURN 0
ELSE
  lnDayNum = tnDay
ENDIF

*** Default to Today if no date passed
IF VARTYPE( tdIndate ) = 'T' AND NOT EMPTY( tdIInDate )
  *** This is a datetime - convert to date - not really necessary as the
  *** functions used will work with DateTime values or Date values, but cleaner.
  ldInDate = TTOD( tdIndate )
ELSE
  ldIndate = IIF ( VARTYPE( tdIndate) # "D" OR EMPTY( tdIndate), DATE(), tdIndate )
ENDIF

*** Get the day of the supplied date
lnCurDay = DOW( ldInDate )

*** Now we need to know the next occurrence of the
*** Required day
IF lnCurDay = lnDayNum
  *** It is the same as the specified day, just add 7 days
  ldRetVal = ldIndate + 7
ELSE
  *** Add the required number of days
  ldRetVal = ldIndate + ( 8 - (7 - lnDayNum ))
ENDIF
RETURN ldRetVal

 

 

 

Answer 9

NextSunday = IIF(8 - DOW(DATE()) < 7,(8 - DOW(DATE())) + DATE(),DATE())

Answer 10

Thanks, that works!

Answer 11

djloa wrote:

This works except for if the day of week is a Sunday.  I suppose I need a case type statement to say if dow is sunday then return DATE else (8 - DOW(DATE())) + DATE().  Not knowing VFP that well, how do I do this?   Cheers

 

What do you mean?

GetNthDay function works whether it is sunday or not. I don't see how this function you propose woudl work when it is July 1st, 2008. It returns July 6th, 2008 when last sunday is July 27th, 2008.

Anyway I think you asked the question wrong from the start.

Answer 12

Sorry for not being clearer, my main aim was to return the next Sunday for a given date (unless date=Sunday), so this formula meets that need. 

However, I would also definitely have a use for a formula that gives the last Sunday of the Month.  My problem though is that I'm a foxpro novice and don't know whether I have ability to create procedure.  I've tried changing date field and using this code in SQL statement but it doesn't work.  I'm sure its my inexperience at fault here. 

Answer 13

You may also try:

 

?Sunday({^2008-8-25})

FUNCTION Sunday(x)

RETURN x+MOD(8-DOW(x),7)

Answer 14

Run this on the table that contains the dates Have a great day

SELE MAX(DATE) as date,;
MONTH(DATE)AS MONTH,;
UPPER(CDOW(DATE)) DAY;
 FROM TABLE ;
WHERE UPPER(CDOW(DATE)) = "SUNDAY" GROUP BY MONTH