Introduction
This article gives an explanation about how to get calendar date in SQL server with Date, Day, Month, Weekday, Quarter, DayofYear, WeekofYear, MonthName, WeekdayName and Vintage based on StartDate and EndDate. Here I also explain how to create a function in SQL server that returns a table with output data.
many developers/programmers/ those who work with Microsoft SQL Server will have at least heard talk about the Table-Valued Function and Calendar Date. Even if any developers/programmers know on a basic level what SQL Server Table-Valued Function do and how to get Date, Day, Month, Weekday, Quarter, DayofYear, WeekofYear, MonthName, WeekdayName based on StartDate and EndDate, they are not always certain when to use Table-Valued Function, it's return type and how to write the code to return Date, Day, Month, Weekday, Quarter, DayofYear, WeekofYear, MonthName, WeekdayName and Vintage based on StartDate and EndDate. So in this article, I Explained everything in detail such as how to create a Table-Valued Function and how to return a table from the Table-Valued Function as well as how to get calendar date based on two different date StartDate and EndDate in SQL Server, but In this article, I will explain all these stuff in details.
In my Previous articles, I Explained Cursor in SQL Server With Syntax and Example and Split Comma Separated String in SQL Server and SQL Server STUFF() Function With Syntax and Example and How to Declare Variables and Set Values in Variable and Basic Performance Tuning Tips and Tricks in SQL Server and Delete/Remove Duplicate Records From Table Using CTE (Common Table Expression) In SQL server and SQL Server Date Range Condition in WHERE Clause and SQL Server Remove Duplicate Records From Table and How to Pass Output Parameter to Stored Procedure in Microsoft SQL Server as well as Create and Execute Dynamic Stored Procedure in SQL Server and How to Fixed SQL Server Restore Database Error Alter Database Mode Single User to Multi-User.
Requirement
1) Create SQL Server Calendar Date Table-Valued Function.
2) Pass StartDate and EndDate as an Input in the Calendar Date Table-Valued Function and Return Table.
3) Select Date, Day, Month, Weekday, Quarter, Day of Year, Week of Year, Name of Month, Name of Weekday and Vintage from the returned Table.
Implementation
Let's we start to write a code to create calendar date function as per our requirement but before that, I am going to show you the basic syntax to create a function in SQL server so you can get more idea about it.
Syntex to Create Table-Valued Function in SQL Server
CREATE FUNCTION Your_function_name ( @parameter_name AS parameter_data_type ) RETURNS TABLE AS RETURN ( --Your select Statment -- )
Now, Let's start to create calendar Date Table-Valued Function to get Date, Day, Month, Weekday, Quarter, DayofYear, WeekofYear, MonthName, WeekdayName and Vintage from input dates as per given requirement.
CREATE FUNCTION [dbo].[Get_Calendar_Date] ( @StartDate DATETIME , @EndDate DATETIME ) RETURNS TABLE AS RETURN ( SELECT Tbl_Obj.RNo , DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) AS [Date] , DATEPART(quarter,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Quarter] , DATEPART(dayofyear,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [DayofYear] , DATEPART(WEEK,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekofYear] , DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Year] , DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Month] , DATEPART(DAY,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Day] , DATEPART(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [Weekday] , DATENAME(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [MonthName] , DATENAME(weekday,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)) AS [WeekdayName] , (RIGHT( REPLICATE('0',(4)) + CONVERT([VARCHAR],DATEPART(YEAR,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0) ,(4) )+ RIGHT( REPLICATE('0',(2)) + CONVERT([VARCHAR],DATEPART(MONTH,DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate)),0) ,(2) ) ) AS [Vintage] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS [RNo] FROM sys.all_objects WITH (NOLOCK) ) Tbl_Obj WHERE DATEADD(DAY,Tbl_Obj.RNo-1,@StartDate) <= @EndDate )
Use of Function
SELECT * FROM Get_Calendar_Date('2019-01-01 00:00:00.000','2019-12-31 00:00:00.000')
Output
Summary
This article gives an explanation about how to get calendar date in SQL server with Date, Day, Month, Weekday, Quarter, DayofYear, WeekofYear, MonthName, WeekdayName and Vintage based on StartDate and EndDate.
