How do I return dates within a stated period yearly

Question

0.00/5 (No votes)

See more:

I am building a SQL report that needs to run yearly from the 31/03/ to 01/04 the next year.

How can I code this in SQL

What I have tried:

sales_date BETWEEN DateAdd(yy,-1,(DateAdd(dd, -1, '01/04/YYYY'))) AND '01/04/YYYY'

Posted 24-Feb-17 1:15am

Member 13021396

Updated 24-Feb-17 5:30am

Add a Solution

Comments

OriginalGriff 24-Feb-17 7:20am

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
That query won't work because 'YYYY' isn't a year, but if you substitute '2016' for it, then it should probably do what you want.
So why is that a problem for you? Or if it isn't, what is a problem? We only get exactly what you type to work from, we get no wider context of your project, so the more detail you give us, the better an answer we can provide.
Use the "Improve question" widget to edit your question and provide better information.

Richard Deeming 24-Feb-17 9:45am

If you ran the report today, would you want it to run from 2017/03/31 to 2018/04/01? Or from 2016/03/31 to 2017/04/01?

If it's a "current financial year" report, I'd expect the latter.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2017-02-24T05:30:00

In opposite to Peter's Leow[^] solution, i'd suggest to use:
- for MS SQL Server 2012 and higher: DATEFROMPARTS (Transact-SQL)[^] function
- for earlier versions - a combination of DATEADD (Transact-SQL)[^], GETDATE()[^], DATEDIFF()[^], etc.

For example:
today:

SQL

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

yesterday:

SQL

DATEADD(day, DATEDIFF(day, 0, GETDATE()), -1)

start of month:

SQL

DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

end of last month:

SQL

DATEADD(month, DATEDIFF(month, 0, GETDATE()), -1)

start of next month:

SQL

DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)

Change it to your needs. Try!

[EDIT]
Tip:

SQL

DECLARE @months INT = 12+(4-MONTH(GETDATE())-12)
SELECT DATEADD(month, DATEDIFF(month, 0, DATEADD(month, @months, GETDATE())), -1) AS FiscalYearStart, 
	DATEADD(month, DATEDIFF(month, 0, DATEADD(month, @months+12, GETDATE())), 0) AS FiscalYearEnd

returns:

FiscalYearStart			FiscalYearEnd
2017-03-31 00:00:00.000	2018-04-01 00:00:00.000

Peter Leow · Answer 2 · 2017-02-24T03:27:00

If I understand you correctly, you are trying to get records between the 31 March of the current year till 1 April of the following year, then
1. Create a datetime for currentyear-3-31 like this:

SELECT CAST(CAST(DATEPART(year, getdate()) AS VARCHAR) + '-3-31' AS DATETIME)

; and
2. Create a datetime for nextyear-4-1 like this:

SELECT CAST(CAST(DATEPART(year, getdate())+1 AS VARCHAR) + '-4-1' AS DATETIME)

See demo: Create Specific Date in TSQL, Sql Server[^]

How do I return dates within a stated period yearly

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0