Create Date in Different Format - In SQL Server

With DatesCreation as
(
select cast('2006-01-01' as datetime) DateValue
union all
select DateValue + 1
from DatesCreation
where DateValue + 1 <= '2008-12-31'

)

select DateValue,Year(DateValue) as Year,Month(DateValue) as Month,day(DateValue) as Day ,

DateName(dw,DateValue) as Day_Name, DateName(Month,DateValue) as Month_Name,

DateName(Year,DateValue) as Year_Name,

Datepart(weekday,DateValue) as [WeekDay],

Datepart(week,DateValue) as Week_In_Year,

DatePart(dayofyear,DateValue) as DayNumber_In_Year,DatePart(quarter,DateValue) as [Quarter],

REPLACE(CONVERT(VARCHAR(10), DateValue, 1), '/', '') as MMDDYY, REPLACE(CONVERT(VARCHAR(10), DateValue, 3), '/', '') as DDMMYY, REPLACE(CONVERT(VARCHAR(10), DateValue, 103), '/', '') AS [DDMMYYYY], CONVERT(VARCHAR(26), DateValue, 109) as [Mon DD YYYY HH:MI:SS:MMMAM (or PM)],

CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY],

CONVERT(VARCHAR(8), DateValue, 3) AS [DD/MM/YY],

CONVERT(VARCHAR(10), DateValue, 103) AS [DD/MM/YYYY],

CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY],

CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY],

CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY],

CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY],

CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD],

CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] from DatesCreation

OPTION (MAXRECURSION 0)

You can also create function something like below to populate dates from One Date to Another Date:

Create function fnDatesCreation (@StartDate datetime, @EndDate datetime)
Returns @Series table ([Day] datetime)
-- select * from dbo.fnDatesCreation ('2008-01-01','2008-01-31')
as
begin
declare @StartingDate datetime
set @StartingDate = @StartDate
while (datediff(day, @StartingDate, @EndDate) >= 0)
begin
insert @Series select @StartingDate
set @StartingDate = dateadd(day,1,@StartingDate)
end
return
end