Solved

Date Query

Posted on 2016-09-02
8
29 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 10

Expert Comment

by:HuaMinChen
Comment Utility
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
Comment Utility
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
Comment Utility
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:ScottPletcher
ScottPletcher earned 166 total points
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now