?
Solved

Date Query

Posted on 2016-09-02
8
Medium Priority
?
60 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 11

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 30

Accepted Solution

by:
Olaf Doschke earned 1336 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 11

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 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 1336 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 664 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

719 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