Mark Wilson
asked on
Date Query
SQL 2008
I have a simple query a them moment
select RDate, month(RDAte) Mth
from Table
This gives me output like the examples below
RDate Mth
01/05/2016 13:00:02 5
31/01/2016 14:25:26 1
The firm am working for have a financial year from May to April of the next year.
I want to add what Financial Year the date is in to my quey and the month number in that financial year
Using the example from above, this would look like
RDate Mth FY FYMth
01/05/2016 13:00:02 5 16/17 1
31/01/2016 14:25:26 1 15/16 9
Thanks
I have a simple query a them moment
select RDate, month(RDAte) Mth
from Table
This gives me output like the examples below
RDate Mth
01/05/2016 13:00:02 5
31/01/2016 14:25:26 1
The firm am working for have a financial year from May to April of the next year.
I want to add what Financial Year the date is in to my quey and the month number in that financial year
Using the example from above, this would look like
RDate Mth FY FYMth
01/05/2016 13:00:02 5 16/17 1
31/01/2016 14:25:26 1 15/16 9
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Olaf - just tried running the query above
I have attached some of the results
For any months with April in the FYMonth = 0 instead of 12
Also for dates
2016-01-01 00:00:00.000
2016-02-01 00:00:00.000
2016-03-01 00:00:00.000
2016-04-01 00:00:00.000
The FY is 16/17 - should be 15/16 - it will be 16/17 from the 1st May onwards - FY May to April the next year
Thanks for your help
Example_Data.PNG
I have attached some of the results
For any months with April in the FYMonth = 0 instead of 12
Also for dates
2016-01-01 00:00:00.000
2016-02-01 00:00:00.000
2016-03-01 00:00:00.000
2016-04-01 00:00:00.000
The FY is 16/17 - should be 15/16 - it will be 16/17 from the 1st May onwards - FY May to April the next year
Thanks for your help
Example_Data.PNG
Hi Halif,
Did you try my example in above?
Now please try
Did you try my example in above?
Now please try
select rdate,month(rdate) Mth,case when month(rdate)>4 then right(cast(year(rdate) as varchar),2)+'/'+right(cast(year(dateadd(yy,1,rdate)) as varchar),2) else right(cast(year(dateadd(yy,-1,rdate)) as varchar),2)+'/'+right(cast(year(rdate) as varchar),2) end as FY, case when month(rdate)>4 then month(rdate)-4 else month(rdate)-4+12 end as FYMonth
from table
ASKER
Sorry didn't see the comment above - will try it now - Thanks for the help
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window