question

tlenzmeier avatar image
tlenzmeier asked

How to Populate Fiscal Day In Year Value

I am trying to populate my fiscal day in year value. My fiscal year starts on March 1, 2XXX and ends on the last day of February. When populating the calendar day in a year, that was pretty straightforward: DATEPART(dayofyear, PKDate). I'm not as up-to-date on while statements as I should be.

sql serverloopingcalendar
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

Here's one way of doing it in-line

declare @calstart datetime = '1 Jan 2016'


select N,
	dateadd(day, N-1, @calstart),
	datepart(dayofyear, dateadd(day, N-1, @calstart)),
	FYDayofYear=datediff(dd,DATEADD(mm,2,dateadd(yy,DATEDIFF(yy,0,DATEADD(mm,-2,dateadd(day, N-1, @calstart))),0)),dateadd(day, N-1, @calstart))+1
from (select row_number()over(order by (select null)) as N from sys.columns) x

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you!

0 Likes 0 ·

I'm missing something. It only returned about 900+ rows or two years worth of dates. I would like to update the entire table. It starts with our earliest transaction Jan 1, 2000

0 Likes 0 ·

Yes it was only showing the idea! If you already have a table of dates, use the same logic to generate the FYDayofYear for your data

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.