Create PROCEDURE [dbo].[ts_Dates]
@isTo bit
AS
/*
-----------------------------------------------------
--Last Dates Display-Value Pair for DropDown List
for date greater than
-----------------------------------------------------
exec [dbo].[ts_Dates] 0
exec [dbo].[ts_Dates] 1
*/
declare @minToDate datetime
declare @minFromDate datetime
select @minToDate = min(<date>) from <table>;
select @minFromDate = DATEADD(MONTH,-1,min(<date>)) from <table>;
--print @minToDate
--print @minFromDate
if @isTo = 1
begin
--Display Value
------------------------------------------- --------------
--February 29, 2012 2/29/2012
--January 31, 2012 1/31/2012
--December 31, 2011 12/31/2011
--with
WITH R(N) AS (
sELECT 0
UNION ALL
SELECT N+1 FROM R
WHERE N < 100 )
, cte2 (Display, Value, [date]) as (
SELECT DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE()))
+ ' ' + CONVERT(VARCHAR(4),DATEPART(Day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,-N,GETDATE()))+1,0))))
+ ', ' + CAST(DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) AS VARCHAR(4)) AS Display
, CONVERT(VARCHAR(4),DATEPART(MONTH,DATEADD(MONTH,-N,GETDATE())))
+ '/'
+ CONVERT(VARCHAR(4),DATEPART(Day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,-N,GETDATE()))+1,0))))
+'/' + CONVERT(VARCHAR(4),DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE()))) AS [Value]
, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,-N,GETDATE()))+1,0)) as [date]
FROM R )
select * from cte2 where [date] > @minToDate
end
else
begin
--Display Value
---------------------------------------- -----------
--February 1, 2012 2/1/2012
--January 1, 2012 1/1/2012
--December 1, 2011 12/1/2011
WITH R(N) AS (
sELECT 0
UNION ALL
SELECT N+1 FROM R
WHERE N < 100 )
, cte2 (Display, Value, [date]) as (
SELECT DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())) + ' 1, ' +
CAST(DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) AS VARCHAR(4)) AS Display
, CONVERT(VARCHAR(4),DATEPART(MONTH,DATEADD(MONTH,-N,GETDATE())))
+ '/1/' + CONVERT(VARCHAR(4),DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE()))) AS [Value]
, DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH,-N,GETDATE())), 0) as [date]
FROM R )
select * from cte2 where [date] > @minFromDate
end
Create
PROCEDURE [dbo].[ts_Dates]
@isTo bit
AS
/*
-----------------------------------------------------
--Last 100 Months Display-Value Pair for DropDown List
-----------------------------------------------------
exec [dbo].[ts_Dates] 0
exec [dbo].[ts_Dates] 1
*/
if @isTo = 1
begin
--Display Value
------------------------------------------- --------------
--February 29, 2012 2/29/2012
--January 31, 2012 1/31/2012
--December 31, 2011 12/31/2011
WITH R(N) AS (
sELECT 0
UNION ALL
SELECT N+1 FROM R
WHERE N < 100 )
SELECT DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE()))
+ ' ' + CONVERT(VARCHAR(4),DATEPART(Day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,-N,GETDATE()))+1,0))))
+ ', ' + CAST(DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) AS VARCHAR(4)) AS Display
, CONVERT(VARCHAR(4),DATEPART(MONTH,DATEADD(MONTH,-N,GETDATE())))
+ '/'
+ CONVERT(VARCHAR(4),DATEPART(Day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATEADD(MONTH,-N,GETDATE()))+1,0))))
+'/' + CONVERT(VARCHAR(4),DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())))
AS [Value]
FROM R
end
else
begin
--Display Value
---------------------------------------- -----------
--February 1, 2012 2/1/2012
--January 1, 2012 1/1/2012
--December 1, 2011 12/1/2011
WITH R(N) AS (
sELECT 0
UNION ALL
SELECT N+1 FROM R
WHERE N < 100 )
SELECT DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())) + ' 1, ' +
CAST(DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) AS VARCHAR(4)) AS Display
, CONVERT(VARCHAR(4),DATEPART(MONTH,DATEADD(MONTH,-N,GETDATE())))
+ '/1/' + CONVERT(VARCHAR(4),DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())))
AS [Value]
FROM R
end
CREATE
FUNCTION [dbo].[tsDatesInRange] (@DateStart DATETIME, @DateEnd DATETIME) RETURNS TABLE AS RETURN (
--=======================================================================================
-- Find first of every month between the start and end dates (inclusive)
--=======================================================================================
--===== Here are the two parameters you wanted
/*
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '20070429',
@DateEnd = '20081201'
select * from tsDatesInRange('20070429', '20081201')
*/
--===== Find the dates using a Tally table as a counter.
-- The outer select formats it. Once cached, it's incredibly fast.
WITH
cteTally
AS
(
--==== Returns a value of 1 to the number of months in date range
SELECT TOP (DATEDIFF(mm,
DATEADD(mm,DATEDIFF(mm,0,@DateStart),0), --First of start month
DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0))) --First of month after end month
N
= ROW_NUMBER() OVER (ORDER BY t1.Object_ID)
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT N,
--DateStart = DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N-1,0),
--NextStart = DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)
month(DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N-1,0)) [Month]
,Year(DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N-1,0)) [Year]
FROM cteTally t
)
GO