Solved

Date Query

Posted on 2016-09-02
8
50 Views
Last Modified: 2016-10-22
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
0
Comment
Question by:halifaxman
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 41782583
Try

declare @date0 as datetime
select @date0=cast('01/05/2016 13:00:02' as datetime) 
select month(@date0),case when month(@date0)>4 then right(cast(year(@date0) as varchar),2)+'/'+right(cast(year(dateadd(yy,1,@date0)) as varchar),2) else right(cast(year(dateadd(yy,-1,@date0)) as varchar),2)+'/'+right(cast(year(@date0) as varchar),2) end as FY, case when month(@date0)>4 then month(@date0)-4 else month(@date0)-4+12 end as FYMonth

select @date0=cast('6/01/2016 18:00:02' as datetime) 
select month(@date0),case when month(@date0)>4 then right(cast(year(@date0) as varchar),2)+'/'+right(cast(year(dateadd(yy,1,@date0)) as varchar),2) else right(cast(year(dateadd(yy,-1,@date0)) as varchar),2)+'/'+right(cast(year(@date0) as varchar),2) end as FY, case when month(@date0)>4 then month(@date0)-4 else month(@date0)-4+12 end as FYMonth

Open in new window

0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 334 total points
ID: 41782585
Modula (%) operation helps here:
Select (5+8)%12; -- is 1
Select (1+8)%12; -- is 9

Open in new window


Also, if the fiscal year month is higher than the real month (which happens for Jan to Apr) your fiscal year is lastyear/thisyear, otherwise thisyear/nextyear.

Putting it together:
Declare @data as table (RDate datetime)

--your sample data (thanks for specifying!)
Insert Into @data values ('20160501 13:00:02'),('20160131 14:25:26')

--SQL2012 or newer
Select  RDate 
, Month(RDate) as Mth 
, Case When (Month(RDate)+8)%12 > Month(RDate) 
  Then Format(DateAdd(yy,-1,RDate),'yy')+'/'+Format(RDate,'yy')
  Else Format(RDate,'yy')+'/'+Format(DateAdd(yy,1,RDate),'yy')
  End as FY
,(Month(RDate)+8)%12 as FYMth From @data

--SQL2008R2 or older
Select  RDate 
, datepart(mm,RDate) as Mth 
, Case When (DatePart(mm,RDate)+8)%12 > DatePart(mm,RDate) 
  Then Right(DateName(yy,DateAdd(yy,-1,RDate)),2)+'/'+ Right(DateName(yy,RDate),2)
  Else Right(DateName(yy,RDate),2)+'/'+ Right(DateName(yy,DateAdd(yy,1,RDate)),2)
  End as FY
,(DatePart(mm,RDate)+8)%12 as FYMth From @data

Open in new window


Notice the comments. To me this is giving your expected results (thanks for specifying!).

Bye, Olaf.
0
 

Author Comment

by:halifaxman
ID: 41782592
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
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 10

Expert Comment

by:HuaMinChen
ID: 41782594
Hi Halif,
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

Open in new window

0
 

Author Comment

by:halifaxman
ID: 41782598
Sorry didn't see the comment above - will try it now - Thanks for the help
0
 
LVL 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 334 total points
ID: 41782602
It would help much more, if you extend the sample code. I just added a date for every month.

You're right about modulo resulting in 0 to 11 instead of 1 to 12, that just needs a minor adjustment:
Select ((5+7)%12)+1; -- is 1
Select ((1+7)%12)+1; -- is 9

Open in new window

The whole result must be shifted by 1, therefore the offset must not be  8, but 7.

I don't know what you did with my FY formula, but it works as needed for me, see here:
fiscal yearEven the previous code was only wrong about April 2016 in regard of FY, just because of the modulo error.
fiscalerr.PNGSo you must have copied or adapted the code wrongly, if you got 16/17 for January to March, too.

Full code:
Declare @data as table (RDate datetime)

--your sample data (thanks for specifying!)
Insert Into @data values 
('20150801 00:00:00'),
('20150901 00:00:00'),
('20151001 00:00:00'),
('20151101 00:00:00'),
('20151201 00:00:00'),
('20160101 00:00:00'),
('20160201 00:00:00'),
('20160301 00:00:00'),
('20160401 00:00:00'),
('20160501 00:00:00'),
('20160601 00:00:00'),
('20160701 00:00:00')

--SQL2012 or newer
Select  RDate 
, Month(RDate) as Mth 
, Case When ((Month(RDate)+7)%12)+1 > Month(RDate) 
  Then Format(DateAdd(yy,-1,RDate),'yy')+'/'+Format(RDate,'yy')
  Else Format(RDate,'yy')+'/'+Format(DateAdd(yy,1,RDate),'yy')
  End as FY
,((Month(RDate)+7)%12)+1 as FYMth From @data

--SQL2008R2 or older
Select  RDate 
, datepart(mm,RDate) as Mth 
, Case When ((DatePart(mm,RDate)+7)%12)+1 > DatePart(mm,RDate) 
  Then Right(DateName(yy,DateAdd(yy,-1,RDate)),2)+'/'+ Right(DateName(yy,RDate),2)
  Else Right(DateName(yy,RDate),2)+'/'+ Right(DateName(yy,DateAdd(yy,1,RDate)),2)
  End as FY
,((DatePart(mm,RDate)+7)%12)+1 as FYMth From @data

Open in new window


Bye, Olaf.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 166 total points
ID: 41786330
Another possible method:

Declare @data as table (RDate datetime)

--your sample data (thanks for specifying!)
Insert Into @data values ('20160501 13:00:02'),('20160131 14:25:26')

--all versions of SQL2005+:
SELECT RDate, month(RDAte) Mth,
    CAST(fy - 2000 AS varchar(2)) + '/' + CAST(fy + 1 - 2000 AS varchar(2)) AS FY,
    ABS(DATEDIFF(MONTH, CAST(fy AS varchar(4)) + '0501', RDate)) + 1 AS FYMth
FROM @data
CROSS APPLY (
    SELECT YEAR(RDate) - CASE WHEN MONTH(RDate) < 5 THEN 1 ELSE 0 END AS fy
) AS assign_alias_names
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question