SQL Query to load date time (Fiscal year) and normal calendar year

Question

Dear friends,

can some one suggest me to create a date table with below conditions please ?

1. First day of every year should be Monday.
2. Monday should be first day of every month
3. column like weektartdate, weekendate,monthstart & monthenddates , QtrStart and End dates and Year start and end dates.
4. create another column in the table to have today =0 , before dates will be with -1, -2,-3 etc and after dates will be 1,2,3 etc.kind of lag columns with in the month.

same to be created for calendar year in addition with fiscal year.
Many thanks in advance.

Answer 1

Hi,@Ramkishore Kommalapudi
For your first and third issue, you can find the answers in this link mentioned by Tom.

For the fourth issue, add this to the select statement:

TheDayDiff = DATEPART(DAYOFYEAR, d)-DATEPART(DAY,GETDATE())  

Finally, for your second question, I don’t quite understand. Since the first day of each month is Monday, there will always be some months that have 4 weeks, and other months that have 5 weeks. How to distinguish these months?

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

See:
https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/