Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

T-SQL query - List this financial years transactions

Posted on 2012-04-10
4
Medium Priority
?
383 Views
Last Modified: 2012-04-11
I would like to list all transactions this financial year (from 31 March).

I have tried something like:-

SELECT     InvDate
FROM         CustInv
WHERE     (InvDate > DATEADD(m, CASE WHEN datepart(m, Getdate()) > 3 THEN 4 ELSE - 8 END - DATEPART(M, GETDATE()), DATEADD(d, 1 - DATEPART(d, GETDATE()),
                      GETDATE())))

However although the date I am getting is 1st April it also includes hours, minutes and seconds - In some cases InvDate is like 2012-04-01 00:00:00.000 and if I am comparing this to above it is not going to include this date.

I guess I could subtract hours, minutes and nanoseconds in the same way as above but thought there might be an easier way.
0
Comment
Question by:donhannam
[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
4 Comments
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 37831504
Since your financial year always start on April 1, you just have to find the proper year - current year or last year.

SELECT   InvDate
FROM     CustInv
WHERE  InvDate >= CONVERT (SmallDateTime, CASE WHEN MONTH(GetDate()) > 3 THEN Convert (varchar(4), YEAR(GetDate())) + '/04/01'
                            ELSE Convert (varchar(4), YEAR(GetDate()) -1) + '/04/01' END)

Note: If InvDate column in CustInv table is DateTime or SmallDateTime, then above CONVERT can be removed to make it look simpler.

Thanks,
Harish
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37832375
It might be best if you do it in a separate step (no points please) as in:

DECLARE @FromDate date =       
            CASE
                  WHEN MONTH(GETDATE()) BETWEEN 1 AND 3 THEN CAST(YEAR(GETDATE()) AS char(4)) + '0401'
                  ELSE CAST(YEAR(GETDATE() + 1) AS char(4)) + '0401'
            END
DECLARE @ToDate date = DATEADD(DAY, -1, DATEADD(Year, 1, @FromDate))

...
      
WHERE     InvDate BETWEEN @FromDate AND @ToDate
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37832671
Easiest to me is to force the time to 00:00:00.000:


WHERE     (InvDate > DATEADD(m, CASE WHEN datepart(m, Getdate()) > 3 THEN 4 ELSE - 8 END - DATEPART(M, GETDATE()), DATEADD(d, 1 - DATEPART(d, GETDATE()),
                      DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))))  --<<-- reset time on GETDATE() to 00:00:00.000
0
 

Author Closing Comment

by:donhannam
ID: 37834681
Thanks for all the comments / ideas - all work but Scotts works best in my case.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In this article I will describe the Backup & Restore 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.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

715 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