To display the whole dates of the current month in sql using CTE
In this article I am going to explain how to display the whole dates of the current month. For example current month is having 30 days, so how to display 1 to 30 days. This can be done many ways but finally I got below solution in my own. Learn how to display the whole dates of the current month in sql using CTE
Learn how to display the whole dates of the current month in sql using CTE
Hi Friends,
In this article I am going to explain how to display the whole dates of the current month?
For example, current date is 23, so just I subtract it using date add function
SELECT DATEADD(D,-23,GETDATE())
Now I got the Firstdate of the current month. Ok how to get last date of the current month; i just change the above query in below way so i got the last date of the current month;
SELECT DATEADD(D,-DATEPART(D,GETDATE()),DATEADD(M,1,GETDATE()))
Last thing is how to display the whole date of the current month ie.first date to last date.so i used common table expression.
WITH CTE AS
(
SELECT DATEADD(D,-DATEPART(D,GETDATE())+1,GETDATE())[FIRST SUNDAY DATE],DATENAME(DW,DATEADD(D,-DATEPART(D,GETDATE())+1,GETDATE()))[DAY NAME]
UNION ALL
SELECT DATEADD(D,1,[FIRST SUNDAY DATE]),DATENAME(DW,DATEADD(D,1,[FIRST SUNDAY DATE]))FROM CTE WHERE [FIRST SUNDAY DATE]<=
DATEADD(D,-DATEPART(D,GETDATE()),DATEADD(M,1,GETDATE()))-1
)
SELECT * FROM CTE
if you have any queries please contact the below mail address:
rathin59117@gmail.com
Thank you for reading my articles
Reference: Rathin813.blogspot.com