Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Date Query

Posted on 2016-09-02
8
Medium Priority
?
64 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 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 70

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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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

971 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