Create an SQL version of this excel formula

1

Hi i hope you can helpme translate this Excel Formula into SQL script.

date in A2 is a date without Time.

1/1/2015

=INT((A2-DATE(YEAR(A2+61)-1,11,1)-WEEKDAY(A2))/7)+2

the field in SQL table is called DateKey.

any help is valued.

2

The question is what do you want from this? I see it takes the current date adds 61 days
DATEADD(DAY, 61, GETDATE())
to find the year
YEAR(DATEADD(DAY, 61, DateKey))
then subtracts a year
YEAR(DATEADD(DAY, 61, DateKey)) - 1
gets a date for November 1
CAST (YEAR(DATEADD(DAY, 61, DateKey)) - 1) AS VARCHAR(4) + '1101'
subtracts the day of the week - this could be a problem if your T-SQL and Excel do not have the same set of numbers
DATEADD(DAY, - DATEPART(dw, DateKey), CAST(CAST (YEAR(DATEADD(DAY, 61, DateKey)) - 1) AS VARCHAR(4) + '1101' AS DATE))
Then you have your DateKey minus your new date
At this point I get lost.

So my question is what do you want?

3

Hi what i am trying to get is the Week number based on a fiscal year that starts on nov 1st of a column of dates from

nov 1st 2006 up to nov first 2030

4

Look at this SELECT DATEDIFF(week, CAST(CAST((YEAR(DATEADD(DAY, 61, GETDATE())) - 1) AS VARCHAR(4)) + '1101' AS DATE), GETDATE())
replace getdate with your column.

5

this worked like a charm by adding +1 at the end... (i was showing the fist week as 0 )