Current Year Calender Script in SQL

Current Year Calender Script in SQL

Below we have two Sql Scripts:

1-Return all Dates and days of a Year same as calender.
2-Return all Months first date along with days Name. i.e 2014-01-01 ,2014-02-01 and so on....

-- Query that will show all dates and days of current year

DECLARE @DateFrom smalldatetime,@DateTo smalldatetime,@DayName nvarchar(100)
SET @DateFrom=cast(DATENAME(yy,getdate()) as DATE)
SET @DateTo=    dateadd(day,-1,dateadd(mm,12,cast(DATENAME(yy,getdate()) as DATE)))

declare  @t table (dt datetime,Days  nvarchar(100))
WHILE (@DateFrom <=@DateTo)
BEGIN
insert into @t
select @DateFrom, datename(WEEKDAY ,@DateFrom)
SET @DateFrom = dateadd(day,1,@DateFrom)
END
select dt  as Dates,Days from @t  

Output -365 Rows

Dates Days
2014-01-01 00:00:00.000 Wednesday
2014-01-02 00:00:00.000 Thursday
2014-01-03 00:00:00.000 Friday
2014-01-04 00:00:00.000 Saturday
2014-01-05 00:00:00.000 Sunday
2014-01-06 00:00:00.000 Monday
2014-01-07 00:00:00.000 Tuesday
2014-01-08 00:00:00.000 Wednesday
2014-01-09 00:00:00.000 Thursday
2014-01-10 00:00:00.000 Friday
2014-01-11 00:00:00.000 Saturday
2014-01-12 00:00:00.000 Sunday
2014-01-13 00:00:00.000 Monday
2014-01-14 00:00:00.000 Tuesday
2014-01-15 00:00:00.000 Wednesday
2014-01-16 00:00:00.000 Thursday
2014-01-17 00:00:00.000 Friday
2014-01-18 00:00:00.000 Saturday
..............................................................................

-- Query that will show all Months 1 date along with Days Name of current year (i.e 2014-01-01 ,2014-02-01 and so on....)

DECLARE @DateFrom smalldatetime,@DateTo smalldatetime,@DayName nvarchar(100)
SET @DateFrom=cast(DATENAME(yy,getdate()) as DATE)
SET @DateTo=    dateadd(day,-1,dateadd(mm,12,cast(DATENAME(yy,getdate()) as DATE)))
declare  @t table (dt datetime,Days  nvarchar(100))
WHILE (@DateFrom <=@DateTo)
BEGIN
insert into @t
select @DateFrom, datename(WEEKDAY ,@DateFrom) 
SET @DateFrom = dateadd(mm,1,@DateFrom)
END
select dt as 'Months',days from @t  

Output

Months                                    Days
2014-01-01 00:00:00.000 Wednesday
2014-02-01 00:00:00.000 Saturday
2014-03-01 00:00:00.000 Saturday
2014-04-01 00:00:00.000 Tuesday
2014-05-01 00:00:00.000 Thursday
2014-06-01 00:00:00.000 Sunday
2014-07-01 00:00:00.000 Tuesday
2014-08-01 00:00:00.000 Friday
2014-09-01 00:00:00.000 Monday
2014-10-01 00:00:00.000 Wednesday
2014-11-01 00:00:00.000 Saturday
2014-12-01 00:00:00.000 Monday