Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

SQL BETWEEN dates for previous 12 months

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
deanlee17
Asked:
deanlee17
  • 14
  • 13
  • 3
1 Solution
 
CluskittCommented:
BETWEEN DATEADD(year,-1,DATEADD(day,1-DAY(DateField),DateField)) AND DATEADD(day,-DAY(DateField),DateField)
0
 
deanlee17Author Commented:
Ok could you explain each step in plain text please?
0
 
deanlee17Author Commented:
We got an error as well...

.Day is not a recognised dateadd option

Thanks.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
CluskittCommented:
.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
 
CluskittCommented:
Also, note that VB.Net will use a different terminology for the day and year parts (it uses DateInterval.Day and DateInterval.Year)
0
 
Pratima PharandeCommented:
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
 
CluskittCommented:
That is exactly what I posted, except you use getdate() instead of a datefield.
0
 
deanlee17Author Commented:
pratima_mcs: This needs to be in the where clause.
0
 
deanlee17Author Commented:
Cluskitt: This is running on a SQL server
0
 
Pratima PharandeCommented:
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
 
CluskittCommented:
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
 
deanlee17Author Commented:
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
 
Pratima PharandeCommented:
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
 
CluskittCommented:
You're missing a ) at the end of the WHERE line. Or, alternately, remove the ( at the beginning of the WHERE.
0
 
deanlee17Author Commented:
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
 
CluskittCommented:
pratima's code needs a ) at the end of the first dateadd line.
0
 
deanlee17Author Commented:
Before the AND statement? Tried it, still errors.
0
 
CluskittCommented:
Just use the one you posted and either remove the ( after WHERE, or place another ) before the GROUP
0
 
deanlee17Author Commented:
Cluskitt, I did, it gives the following...

 'SysproCompanyA.dbo.SRH_THV2.DAY is not a recognised DateADD option
0
 
CluskittCommented:
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
 
deanlee17Author Commented:
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
 
deanlee17Author Commented:
SysproCompanyA.dbo.SRH_THV2 does have DAY in there but its not being linked to or used in any way
0
 
CluskittCommented:
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
 
CluskittCommented:
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
 
deanlee17Author Commented:
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
 
CluskittCommented:
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
 
deanlee17Author Commented:
It complained about the year as well, changed that to a Y.

It now has no errors but doesnt return any records.
0
 
deanlee17Author Commented:
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
 
CluskittCommented:
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
 
deanlee17Author Commented:
Lovely, thanks alot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 14
  • 13
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now