Solved

T-SQL query - List this financial years transactions

Posted on 2012-04-10
4
377 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
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 69

Accepted Solution

by:
Scott Pletcher earned 500 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 37
T-SQL: New to using transactions 9 46
Comparison query - 4 columns 9 21
shrink table after huge delete 2 13
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 we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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