SQL query Calendar

This thing is reserved for itself.

Often use some queries need to do all-month statistics, but sometimes the statistics need to show the date not to occur, so you will need a fixed date table, (T6 's custom query estimation is also required, at least as before)

Here are two ways to get the date table for a given month, the first one for SQL 2000 and above, and the second only for SQL 2005 and later databases

Function One:

--Support SQL 2000

CREATE FUNCTION dbo. Getcalendar (@Begin NVARCHAR (30))

RETURNS @rst TABLE (ddate smalldatetime,idx int IDENTITY () not NULL)

As

BEGIN

IF ISNULL (@Begin, ') = ' RETURN '

INSERT into @rst

SELECT TOP (DATEDIFF (Day, @Begin, DATEADD (month,1, @Begin)-1) +1) @Begin

From syscolumns

UPDATE @rst SET ddate=ddate+idx-1

RETURN

END

GO

How to use:

SELECT * FROM dbo. Getcalendar (' 2015-6-1 ')

Function Two:

--Requires support for row_number functions, supports SQL 2005 and later versions of database

CREATE FUNCTION dbo. GetCalendar2005 (@Begin NVARCHAR (30))

RETURNS @rst TABLE (ddate smalldatetime)

As

BEGIN

INSERT into @rst

SELECT TOP (DATEDIFF (Day, @Begin, DATEADD (month,1, @Begin)-1) +1) CAST (@Begin as smalldatetime) -1+row_number () over ( ORDER by ID)

From

syscolumns

RETURN

END

GO

How to use:

SELECT * FROM dbo. GetCalendar2005 (' 2015-6-1 ')

SQL query Calendar