How to calculate date difference between two dates without sunday? 1

[Niki_S]

I want to get date difference between two days without sunday. It is something like this,

2022 - 08 - 20 (Saturday)
2022 - 08 - 23 (Tuesday)
2022 - 08 - 24 (Wednesday)
​

If I input 2022 - 08 - 22 as my date I want to take the difference as 1.
How can I get the difference without sunday?

Thank you.

 

[mjcmkrsr]

Hi,

You may try the code below and adapt to your needs.

LOCAL liDays2Substract, lnI

dDateStart = DATE() - 1
dDateEnd = DATE() + 13

liDays2Substract = 0

FOR lnI = 0 TO dDateEnd - dDateStart
	IF DOW(dDateStart + lnI) = 1 && You have to check whether you set Sunday as 1st day of Week - otherwise you'll have to change
		liDays2Substract = liDays2Substract + 1
	ENDIF
ENDFOR

WAIT WINDOW + "Number of days between " + TRANSFORM(dDateStart) +" and "+ TRANSFORM(dDateEnd) + " : " + ALLTRIM(STR(dDateEnd - dDateStart - liDays2Substract)) TIMEOUT 5


RETURN

hth

MarK

 

[Niki_S]

I used this,

LOCAL liDays2Substract, lnI

	dDateStart = _LastDay.dDate - 1
	dDateEnd = ThisForm.txtDate.value + 13

	liDays2Substract = 0

	[highlight #FCE94F]FOR lnI = 0 TO dDateEnd - dDateStart[/highlight]
		IF DOW(dDateStart + lnI) = 1 
			liDays2Substract = liDays2Substract + 1
		ENDIF
	ENDFOR

I got an error in highlighted line as "operator type mismatch". How can I fix this?

Thank you.

 

[mjcmkrsr]

Hi Niki,

Then please check the VARTYPE() of your dDateStart and dDateEnd. The results should/must be "D"

hth

MarK

 

[Niki_S]

I'm taking dDateStart as this,

SQLExec(HrisHandle,"SELECT max(dDate) as dDate FROM HRIS_TEMP_FactAttendance WHERE dDate <?ThisForm.txtDate.value And nEmpNo =?Absent_New.nempno ",'_LastDay')

and dDateEnd as below.

ThisForm.txtDate.value

 

[mjcmkrsr]

Hi,

I'm sorry but that's not what I asked you to check

Please insert in the code right below dDateEnd

wait window + VARTPYE(dDateStart) + " - " VARTYPE(dDateEnd)

The result should twice be "D"

hth

MarK

 

[Chris Miller]

Do this within SQL Server. Solution 2 on

https://www.codeproject.com/Questions/727664/Getting-total-days-in-month-excluding-Sundays

SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 1) --EDIT give 2 instead of 1 to exclude saturday also
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)

Instead of variables @StartDate and @EndDate you can also use date fields. They just have to be of the type date or datetime, not character.

If you want to calculate with dates (in your case differences), ensure you actually store dates and datetimes in their data type and not as strings. Same in T-SQL as in VFP or elsewhere, there are no string calculations, unless there would be implicit conversion of string to date or datetime.

Chriss

 

[mjcmkrsr]

...

and a simpler and pimped-up function for VFP users

LPARAMETERS tdDateStart, tdDateEnd

LOCAL liSundays, liNoSundays

liNoSundays = 0

IF VARTYPE(tdDateStart) = "D" AND VARTYPE(tdDateEnd) = "D" AND tdDateStart < tdDateEnd

	liSundays = 0

	FOR i = 0 TO tdDateEnd - tdDateStart
		IF CDOW(tdDateStart + i) = "Sunday"
			liSundays = liSundays + 1
			
		ENDIF
	ENDFOR
	
	liNoSundays = tdDateEnd - tdDateStart - liSundays 
	
	WAIT WINDOW + "Number of days between " + TRANSFORM(tdDateStart) +" and "+ TRANSFORM(tdDateEnd) + " : " + ALLTRIM(STR(tdDateEnd - tdDateStart)) ;
				+ CHR(13) + "Number of Sundays between " + TRANSFORM(tdDateStart) +" and "+ TRANSFORM(tdDateEnd) + " : " + ALLTRIM(STR(liSundays )) ;
				+ CHR(13) + "Number of other days : " + ALLTRIM(STR(liNoSundays)) TIMEOUT 5

ELSE 

	= MESSAGEBOX("Wrong parameters", 16, "Checking parameters") 

ENDIF 

RETURN liNoSundays

hth

MarK

 

[TamarGranor]

Seems to me you should be able to do this without a loop. Something along these lines:

FUNCTION DaysBetween(tdStart, tdEnd)
* Count days between, omitting Sundays

LOCAL lnDaysBetween, lnSundays

lnDaysBetween = m.tdEnd - m.tdStart

* Now figure out how many Sundays.
lnSundays = INT(lnDaysBetween/7)
IF DOW(m.tdStart) > DOW(m.tdEnd)
  lnSundays = m.lnSundays + 1
ENDIF

lnDaysBetween = m.lnDaysBetween - m.lnSundays

RETURN m.lnDaysBetween

Very lightly tested, but I think it's right. The advantage of this is that no matter how far apart the dates are, it should be very quick.

Tamar

 

[mjcmkrsr]

Hi Tamar

Yet another approach

	liSundays = 0
	liNoSundays = 0

	FOR i = 0 TO 6
		IF CDOW(tdDateStart + i) = "Sunday"
			ldNextSunday = tdDateStart + i
			
		ENDIF
	ENDFOR
	
	liSundays = 1 + INT((tdDateEnd - ldNextSunday) / 7)
	liNoSundays = tdDateEnd - tdDateStart - liSundays

MarK