Round up date calc

Guys,

How do I round this UP to the actual number of weeks ?

datediff(d,c.startdate,c.enddate)/7 as ContractPeriod

Example, 10/10/05 - 15/06/07 is 87.57 weeks, I need to show this as 88.

Thanks
Bob

Try

VB Code:

Private Sub Command1_Click()
MsgBox Round(87.57)
End Sub

Sorry, my mistake, I meant in my SQL view....

ALTER view vw_Rpt_ContractorReport
AS
SELECT cr.*,
c.ID AS Contract_ID,
c.name as Contract_Name,
c.startdate as ContractStartDate,
c.enddate as ContractEndDate,
datediff(d,c.startdate,c.enddate)/7 as ContractPeriod
FROM ContractorReport cr
Join Contract c on c.id = cr.Contract

As the functions available vary significantly by DBMS, we will need to know which database system you are using.

Thanks for the response Si,

I am using SQL Server 2000

Bob

Have you tried using ww as datepart parameter for the DATEDIFF function?

Code:

---

DATEDIFF(ww,c.startdate,c.enddate)

---

Good point, but I don't think that would round up.

I've just remembered a little trick tho, how about this:

(datediff(d,c.startdate,c.enddate)+6)/7 as ContractPeriod