Calcolare la differenza tra due date in sql

declare @data1 datetime
declare @data2 datetime

set @data1 = '20080101'
set @data2 = '20080315'

select DATEDIFF(day,@data1,@data2)

/*
-----------
74
*/

USE tempdb;

CREATE TABLE dbo.Tabella(
num_persone int NOT NULL,
data_arrivo smalldatetime NOT NULL,
data_partenza smalldatetime NOT NULL
);

INSERT dbo.Tabella VALUES(5, '20080309', '20080311');
INSERT dbo.Tabella VALUES(4, '20080310', '20080317');

SELECT num_persone * DATEDIFF(day, data_arrivo, data_partenza) AS Tot
FROM dbo.Tabella;

/* Output:

Tot
-----------
10
28

(2 row(s) affected)

*/

DROP TABLE dbo.Tabella;