Just for fun I took this ball and ran with it...here's a procedure I whipped up that counts the number of periods between any two dates for years, quarters, weeks, etc. I then got totally carried away with it and threw in support for the list of languages on my machine. By putting in a language code the procedure will do a simple lookup to get the proper DATEFORMAT setting (DMY,MDY, etc). It still needs some heavy-duty testing, but it was a good brain teaser.
CREATE PROCEDURE dbo.GetDateTimeIntervals
@LanguageID INT
,@StartDate DATETIME
,@EndDate DATETIME
,@TimeInterval NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
SET LANGUAGE English
SET DATEFORMAT MDY
IF OBJECT_ID('tempdb..#LanguageXref') IS NOT NULL
DROP TABLE #LanguageXref
CREATE TABLE #LanguageXref (
[LangID] INT NOT NULL,
[dateformat] CHAR(3) NULL,
[datefirst] INT NULL,
[name] NVARCHAR(50),
[alias] NVARCHAR(50),
PRIMARY KEY ([LangID]))
DECLARE
@LangID INT
,@DateFormat CHAR(3)
,@Alias NVARCHAR(50)
,@CurrDate DATETIME
SET @CurrDate = (SELECT GETDATE())
IF NULLIF(@StartDate,'') IS NULL
SET @StartDate = CAST('1900-01-01' AS DATETIME)
IF NULLIF(@EndDate,'') IS NULL
SET @EndDate = CAST(@CurrDate AS DATETIME)
SET @LangID = @LanguageID
INSERT INTO #LanguageXref
SELECT * FROM (
VALUES
(0,'mdy',7,'us_english','English'),
(1,'dmy',1,'Deutsch','German'),
(2,'dmy',1,'Français','French'),
(3,'ymd',7,'???','Japanese'),
(4,'dmy',1,'Dansk','Danish'),
(5,'dmy',1,'Español','Spanish'),
(6,'dmy',1,'Italiano','Italian'),
(7,'dmy',1,'Nederlands','Dutch'),
(8,'dmy',1,'Norsk','Norwegian'),
(9,'dmy',7,'Português','Portuguese'),
(10,'dmy',1,'Suomi','Finnish'),
(11,'ymd',1,'Svenska','Swedish'),
(12,'dmy',1,'ceština','Czech'),
(13,'ymd',1,'magyar','Hungarian'),
(14,'dmy',1,'polski','Polish'),
(15,'dmy',1,'româna','Romanian'),
(16,'ymd',1,'hrvatski','Croatian'),
(17,'dmy',1,'slovencina','Slovak'),
(18,'dmy',1,'slovenski','Slovenian'),
(19,'dmy',1,'e???????','Greek'),
(20,'dmy',1,'?????????','Bulgarian'),
(21,'dmy',1,'???????','Russian'),
(22,'dmy',1,'Türkçe','Turkish'),
(23,'dmy',1,'British','British English'),
(24,'dmy',1,'eesti','Estonian'),
(25,'ymd',1,'latviešu','Latvian'),
(26,'ymd',1,'lietuviu','Lithuanian'),
(27,'dmy',7,'Português (Brasil)','Brazilian'),
(28,'ymd',7,'????','Traditional Chinese'),
(29,'ymd',7,'???','Korean'),
(30,'ymd',7,'????','Simplified Chinese'),
(31,'dmy',1,'Arabic','Arabic'),
(32,'dmy',7,'???','Thai')) AS vtable
([langid],[dateformat],[datefirst],[name],[alias])
SET @DateFormat = ''
SET @Alias = N''
SELECT
@DateFormat = [dateformat]
,@Alias = [alias]
FROM
#LanguageXref
WHERE
[LangID] = @LangID
DECLARE
@dts DATETIME
,@dte DATETIME
,@ddif NVARCHAR(20)
,@ti NVARCHAR(255)
SET @dts = ''
SET @dte = ''
SET @ti = ''
BEGIN
SELECT QUOTENAME('SET DATEFORMAT '+@DateFormat,'''') AS [DateFormat]
SELECT QUOTENAME('SET LANGUAGE '+@Alias,'''') AS [Language]
SET @dts = @StartDate
SELECT CAST(@StartDate AS DATETIME) AS StartDate
SET @dte = @EndDate
SELECT CAST(@EndDate AS DATETIME) AS EndDate;
DECLARE
@ParmDefinition NVARCHAR(100)
,@result BIGINT
SET @ti = QUOTENAME(N'SELECT @result = DATEDIFF('+@TimeInterval+','''+CONVERT(NVARCHAR(30),@EndDate,121)+''','''+CONVERT(NVARCHAR(30),@StartDate,121)+''')')
SET @ParmDefinition = N'@result BIGINT OUTPUT'
SET @ti = REPLACE(REPLACE(@ti,'[',''),']','')
EXECUTE sp_executesql @ti, @ParmDefinition, @result = @ddif OUTPUT;
IF @TimeInterval IN ('y','yy','yyyy') SET @TimeInterval = 'YEAR'
IF @TimeInterval IN ('q','qq') SET @TimeInterval = 'QUARTER'
IF @TimeInterval IN ('m','mm') SET @TimeInterval = 'MONTH'
IF @TimeInterval IN ('wk','ww') SET @TimeInterval = 'WEEK'
IF @TimeInterval IN ('dd','dy','d') SET @TimeInterval = 'DAY'
IF @TimeInterval IN ('hh') SET @TimeInterval = 'HOUR'
IF @TimeInterval IN ('mi') SET @TimeInterval = 'MINUTE'
IF @TimeInterval IN ('s','ss') SET @TimeInterval = 'SECOND'
IF @TimeInterval IN ('ms') SET @TimeInterval = 'MILLISECOND'
IF @TimeInterval IN ('n') SET @TimeInterval = 'NANOSECOND'
IF @TimeInterval IN ('weekday','dw') SET @TimeInterval = 'WEEKDAY'
IF @TimeInterval IN
('YEAR','QUARTER','MONTH','WEEK','DAY','HOUR','MINUTE','SECOND','MILLISECOND',
'y','yy','yyyy','qq','q','mm','m','wk','ww','dd','d','dy','weekday','dw','hh',
'mi','n','ss','s','ms')
BEGIN
SELECT QUOTENAME(@ddif+' '+UPPER(@TimeInterval)+'(S)','''') AS [Time Units];
END
SET DATEFORMAT MDY
SET LANGUAGE English
END
/*
EXEC dbo.SetLanguage 0,'2/5/1967','2013-22-11','yy'
EXEC dbo.SetLanguage 1,'2/5/1967','2001/7/5','qq'
EXEC dbo.SetLanguage 2,'2/5/1967','2013-22-11','mm'
EXEC dbo.SetLanguage 3,'2/5/1967','2013-22-11','wk'
EXEC dbo.SetLanguage 11,'2/5/1967','2013-22-11','dd'
EXEC dbo.SetLanguage 14,'2/5/1967','2013-22-11','hh'
EXEC dbo.SetLanguage 19,'2/5/1967','2013-22-11','ss'
EXEC dbo.SetLanguage 32,'2/5/1967','2013-22-11','dw'
EXEC dbo.SetLanguage 0,'1967-05-08','','yy'
EXEC dbo.SetLanguage 0,'','1967-05-08','yy'
EXEC dbo.SetLanguage 0,'','','yy'
*/
END
Sample Output
<?xml version="1.0" ?>
<RESULTS1>
<RECORD>
<DateFormat>'SET DATEFORMAT dmy'</DateFormat>
</RECORD>
</RESULTS1>
<RESULTS2>
<RECORD>
<Language>'SET LANGUAGE Greek'</Language>
</RECORD>
</RESULTS2>
<RESULTS3>
<RECORD>
<StartDate>1967-05-02 00:00:00.000</StartDate>
</RECORD>
</RESULTS3>
<RESULTS4>
<RECORD>
<EndDate>2013-11-22 00:00:00.000</EndDate>
</RECORD>
</RESULTS4>
<RESULTS5>
<RECORD>
<Time Units>'1469318400 SECOND(S)'</Time Units>
</RECORD>
</RESULTS5>