Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

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

Posted on Tuesday, January 31, 2012 6:43 AM SQL Server 2005 Tricks , SQL Server 2000 Tricks | Back to top


Comments on this post: Last n Months Display-Value Pair for DropDown List greater than a minimumm date

# re: Last n Months Display-Value Pair for DropDown List greater than a minimumm date
Requesting Gravatar...
I am still unable to show proper drop down list greater than a minimum date, may be somewhere i am mistaken :S
Left by Web hosting in Chile on Apr 30, 2012 2:03 AM

# re: Last n Months Display-Value Pair for DropDown List greater than a minimumm date
Requesting Gravatar...
There's a lot of new changes everyday. Quite an amazing page in here indeed. thanks a lot for sharing..
Left by carpet cleaners on Jul 23, 2012 1:21 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net