I want all the dates in all months in given year

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

I want all the dates in all months in given year

Author

Topic

itnagaraj
Yak Posting Veteran

70 Posts

Posted - 2010-10-22 : 04:42:28

I want all the daates and dayname in all monyhs in a given year.

Declare @year = 2009

i want result as following

Date Day Name
---------------------
01-01-2010 Friday
02-01-2010 Saturday
----
----
Upto
30-12-2010 Thursady
31-12-2010 Friday

V.NAGARAJAN

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-10-22 : 05:15:35

Try This -


DECLARE @Year as int
DECLARE @tblCalendar AS TABLE ( Date VARCHAR(10), DayName VARCHAR(10) )
SET @Year = 2009
DECLARE @StartDate AS DateTime, @EndDate AS DateTime, @TempDate AS DateTime
SET @StartDate = '01-Jan-'+ CONVERT(VARCHAR(4), @Year)
SET @EndDate = '31-Dec-' + CONVERT(VARCHAR(4), @Year)
SET @TempDate = @StartDate
WHILE @TempDate <= @EndDate
BEGIN
	INSERT INTO @tblCalendar
	SELECT CONVERT(VARCHAR(10), @TempDate, 105), DATENAME(WEEKDAY, @TempDate)
	SET @TempDate = DATEADD(DAY,1, @TempDate)
END
SELECT * FROM @tblCalendar

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-10-22 : 05:20:02

TRY THIS,

DECLARE @YEAR AS INT
DECLARE @SDATE AS DATETIME
SET @YEAR = 2010
DECLARE @EDATE AS DATETIME
SET @SDATE = CONVERT(DATETIME,('1/1/' + CONVERT(VARCHAR,@YEAR)))
SET @EDATE = CONVERT(DATETIME,('12/31/' + CONVERT(VARCHAR,@YEAR)))
CREATE TABLE #TEMP (DATEVal VARCHAR(100),DAYN VARCHAR(30))
WHILE @SDATE <= @EDATE
BEGIN
INSERT INTO #TEMP VALUES (CONVERT(VARCHAR,@SDATE,105),DATENAME(DW,@SDATE))
SET @SDATE = DATEADD(D,1,@SDATE)
END
SELECT * FROM #TEMP
DROP TABLE #TEMP

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources