Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL BETWEEN dates for previous 12 months

Posted on 2012-03-22
30
Medium Priority
?
289 Views
Last Modified: 2012-06-22
Hi Experts,

Ok as the title suggests I need the a min and max date for a between statement. However there is a slight catch.

If the current date is 22/03/2012 then I need the min date to be:

1/03/2011

and the max date to be

28/02/2012

Many Thanks.
0
Comment
Question by:deanlee17
[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
  • 14
  • 13
  • 3
30 Comments
 
LVL 18

Accepted Solution

by:
Cluskitt earned 2000 total points
ID: 37751619
BETWEEN DATEADD(year,-1,DATEADD(day,1-DAY(DateField),DateField)) AND DATEADD(day,-DAY(DateField),DateField)
0
 

Author Comment

by:deanlee17
ID: 37751635
Ok could you explain each step in plain text please?
0
 

Author Comment

by:deanlee17
ID: 37751659
We got an error as well...

.Day is not a recognised dateadd option

Thanks.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751701
.Day? There is no dot on the function.
This works for both VB and SQL. Basically, what you do is:
1- Subtract the number of days+1 (22-03-2012 subtracts 21 days, becoming 01-03-2012), then subtract one year
2- Subtract the number of days (22-03-2012 subtracts 22 days, becoming 29-02-2012)
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751706
Also, note that VB.Net will use a different terminology for the day and year parts (it uses DateInterval.Day and DateInterval.Year)
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 37751718
try like this you will get required dated in from date nad todate

select getdate() as Todays_date  , DATEADD(year,-1,DATEADD(day,1-DAY(getdate()) ,getdate())) as From_date, DATEADD(day,-DAY(getdate()),getdate()) as To_date
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751729
That is exactly what I posted, except you use getdate() instead of a datefield.
0
 

Author Comment

by:deanlee17
ID: 37751731
pratima_mcs: This needs to be in the where clause.
0
 

Author Comment

by:deanlee17
ID: 37751736
Cluskitt: This is running on a SQL server
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 37751744
yes Cluskitt , I have change format only

Select * from tablename

where date_field between DATEADD(year,-1,DATEADD(day,1-DAY(getdate()) ,getdate()))  and DATEADD(day,-DAY(getdate()),getdate())
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751759
If you want the current date, just replace DateField with GETDATE(). I've supplied only the between part based on a DateField as input. The theory is the same however you use it, though, and it's as I've explained above.
The provided syntax should work on any SQL, and certainly will run on a MS SQL server (I've tested it).
0
 

Author Comment

by:deanlee17
ID: 37751766
Ok im getting errors. My full code is below. Errors are

Error in list of function arguments: 'GROUP' not recognized.
Error in list of function arguments: ',' not recognized.
Unable to parse query text.



SELECT     TOP (100) PERCENT SysproCompanyA.dbo.SRH_THV2.Customer, COUNT(SysproCompanyA.dbo.SRH_THV2.Customer) AS CustLineCount, 
                      SUM(SysproCompanyA.dbo.SRH_THV2.MQtyToDispatch) AS TotalQty, dbo.tblCalendar.Month, dbo.tblCalendar.Year
FROM         SysproCompanyA.dbo.SRH_THV2 INNER JOIN
                      dbo.Top50Suppliers ON SysproCompanyA.dbo.SRH_THV2.Customer = dbo.Top50Suppliers.SupplierCode CROSS JOIN
                      dbo.tblCalendar
WHERE     (SysproCompanyA.dbo.SRH_THV2.ActualDeliveryDate between DATEADD(year,-1,DATEADD(day,1-DAY(getdate()) ,getdate()))  and DATEADD(day,-DAY(getdate()),getdate())

GROUP BY SysproCompanyA.dbo.SRH_THV2.Customer, dbo.tblCalendar.Month, dbo.tblCalendar.Year, dbo.tblCalendar.MonthNo
ORDER BY dbo.tblCalendar.Year, dbo.tblCalendar.MonthNo

Open in new window



Thanks.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 37751794
try this

SELECT     TOP (100) PERCENT SysproCompanyA.dbo.SRH_THV2.Customer, COUNT(SysproCompanyA.dbo.SRH_THV2.Customer) AS CustLineCount,
                      SUM(SysproCompanyA.dbo.SRH_THV2.MQtyToDispatch) AS TotalQty, dbo.tblCalendar.Month, dbo.tblCalendar.Year
FROM         SysproCompanyA.dbo.SRH_THV2 INNER JOIN
                      dbo.Top50Suppliers ON SysproCompanyA.dbo.SRH_THV2.Customer = dbo.Top50Suppliers.SupplierCode CROSS JOIN
                      dbo.tblCalendar
WHERE     SysproCompanyA.dbo.SRH_THV2.ActualDeliveryDate between DATEADD(year,-1,DATEADD(day,1-DAY(getdate()) ,getdate())
 and DATEADD(day,-DAY(getdate()),getdate())

GROUP BY SysproCompanyA.dbo.SRH_THV2.Customer, dbo.tblCalendar.Month, dbo.tblCalendar.Year, dbo.tblCalendar.MonthNo
ORDER BY dbo.tblCalendar.Year, dbo.tblCalendar.MonthNo
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751796
You're missing a ) at the end of the WHERE line. Or, alternately, remove the ( at the beginning of the WHERE.
0
 

Author Comment

by:deanlee17
ID: 37751799
Pratima, your code gives:

Error in list of function arguments: 'AND' not recognized.
Error in list of function arguments: 'GROUP' not recognized.
Unable to parse query text.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751808
pratima's code needs a ) at the end of the first dateadd line.
0
 

Author Comment

by:deanlee17
ID: 37751818
Before the AND statement? Tried it, still errors.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751824
Just use the one you posted and either remove the ( after WHERE, or place another ) before the GROUP
0
 

Author Comment

by:deanlee17
ID: 37751831
Cluskitt, I did, it gives the following...

 'SysproCompanyA.dbo.SRH_THV2.DAY is not a recognised DateADD option
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751865
Ok, just to debug things, in the where clause use a simple =getdate()
When you get that to work (probably will return no records), then replace it with the between clause provided above.
You're getting some syntax error in a weird place, because I don't see any SysproCompanyA.dbo.SRH_THV2.DAY in the whole query, let alone on the DATEADD function.
Are those tables or views/procedures you're working with?
0
 

Author Comment

by:deanlee17
ID: 37751885
Yes i agree the errors are slightly odd. Ok changed the where clause to...

WHERE     (SysproCompanyA.dbo.SRH_THV2.ActualDeliveryDate > '2012-01-01')

and it runs fine.

SysproCompanyA.dbo.SRH_THV2 is a view, the other 2 are tables.
0
 

Author Comment

by:deanlee17
ID: 37751891
SysproCompanyA.dbo.SRH_THV2 does have DAY in there but its not being linked to or used in any way
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751894
So, what happens if you replace:
> '2012-01-01'
-note that I left the ) in there- with:
BETWEEN DATEADD(year,-1,DATEADD(day,1-DAY(GETDATE()),GETDATE())) AND DATEADD(day,-DAY(GETDATE()),GETDATE())
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751903
Ok, I know what happened then. SQL is interpreting day as the field in the view, not the function. The easiest way to fix it is to change DATEADD(day with DATEADD('d'
0
 

Author Comment

by:deanlee17
ID: 37751927
Ok I think we are getting there, but we have a new error.

Invalid parameter 1 specified for dateadd

Tried removing the first bracket after WHERE and I got....

Unable to parse query text.

Code is currently...

SELECT     TOP (100) PERCENT SysproCompanyA.dbo.SRH_THV2.Customer, COUNT(SysproCompanyA.dbo.SRH_THV2.Customer) AS CustLineCount, 
                      SUM(SysproCompanyA.dbo.SRH_THV2.MQtyToDispatch) AS TotalQty, dbo.tblCalendar.Month, dbo.tblCalendar.Year
FROM         SysproCompanyA.dbo.SRH_THV2 INNER JOIN
                      dbo.Top50Suppliers ON SysproCompanyA.dbo.SRH_THV2.Customer = dbo.Top50Suppliers.SupplierCode CROSS JOIN
                      dbo.tblCalendar
WHERE     (SysproCompanyA.dbo.SRH_THV2.ActualDeliveryDate BETWEEN DATEADD(SysproCompanyA.dbo.SRH_THV2.YEAR, - 1, DATEADD('d', 1 - DAY(GETDATE()), 
                      GETDATE())) AND DATEADD('d', - DAY(GETDATE()), GETDATE()))
GROUP BY SysproCompanyA.dbo.SRH_THV2.Customer, dbo.tblCalendar.Month, dbo.tblCalendar.Year, dbo.tblCalendar.MonthNo
ORDER BY dbo.tblCalendar.Year, dbo.tblCalendar.MonthNo

Open in new window

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751932
Sorry, that was my bad. Remove the '
DATEADD(d,
It should work now. But in the future, try to avoid naming your fields the same as sql functions. It could lead to confusion, such as this.
0
 

Author Comment

by:deanlee17
ID: 37751948
It complained about the year as well, changed that to a Y.

It now has no errors but doesnt return any records.
0
 

Author Comment

by:deanlee17
ID: 37751976
Its defo the where clause, if i change to:

WHERE     (SysproCompanyA.dbo.SRH_THV2.ActualDeliveryDate BETWEEN '2011-01-01' AND '2012-01-01')

we get results, obv some of these results would fall within the where clause that we are trying to achieve.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37751978
y is for day of the year. You need to use yy.
This page explains in detail:
http://msdn.microsoft.com/en-us/library/ms186819.aspx
0
 

Author Comment

by:deanlee17
ID: 37752394
Lovely, thanks alot.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

618 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