SQL Server - Calendar Date (Date, Day, Month, Weekday)

watch_later Sunday, March 17, 2019

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.

SQL Server - Calendar Date (Date, Day, Month, Weekday, Quarter, DayofYear, WeekofYear, MonthName, WeekdayName)

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.


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.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.



sentiment_satisfied Emoticon