deanlee17
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We got an error as well...
.Day is not a recognised dateadd option
Thanks.
.Day is not a recognised dateadd option
Thanks.
.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)
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)
Also, note that VB.Net will use a different terminology for the day and year parts (it uses DateInterval.Day and DateInterval.Year)
try like this you will get required dated in from date nad todate
select getdate() as Todays_date , DATEADD(year,-1,DATEADD(da y,1-DAY(ge tdate()) ,getdate())) as From_date, DATEADD(day,-DAY(getdate() ),getdate( )) as To_date
select getdate() as Todays_date , DATEADD(year,-1,DATEADD(da
That is exactly what I posted, except you use getdate() instead of a datefield.
ASKER
pratima_mcs: This needs to be in the where clause.
ASKER
Cluskitt: This is running on a SQL server
yes Cluskitt , I have change format only
Select * from tablename
where date_field between DATEADD(year,-1,DATEADD(da y,1-DAY(ge tdate()) ,getdate())) and DATEADD(day,-DAY(getdate() ),getdate( ))
Select * from tablename
where date_field between DATEADD(year,-1,DATEADD(da
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).
The provided syntax should work on any SQL, and certainly will run on a MS SQL server (I've tested it).
ASKER
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.
Thanks.
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
Thanks.
try this
SELECT TOP (100) PERCENT SysproCompanyA.dbo.SRH_THV 2.Customer , COUNT(SysproCompanyA.dbo.S RH_THV2.Cu stomer) AS CustLineCount,
SUM(SysproCompanyA.dbo.SRH _THV2.MQty ToDispatch ) AS TotalQty, dbo.tblCalendar.Month, dbo.tblCalendar.Year
FROM SysproCompanyA.dbo.SRH_THV 2 INNER JOIN
dbo.Top50Suppliers ON SysproCompanyA.dbo.SRH_THV 2.Customer = dbo.Top50Suppliers.Supplie rCode CROSS JOIN
dbo.tblCalendar
WHERE SysproCompanyA.dbo.SRH_THV 2.ActualDe liveryDate between DATEADD(year,-1,DATEADD(da y,1-DAY(ge tdate()) ,getdate())
and DATEADD(day,-DAY(getdate() ),getdate( ))
GROUP BY SysproCompanyA.dbo.SRH_THV 2.Customer , dbo.tblCalendar.Month, dbo.tblCalendar.Year, dbo.tblCalendar.MonthNo
ORDER BY dbo.tblCalendar.Year, dbo.tblCalendar.MonthNo
SELECT TOP (100) PERCENT SysproCompanyA.dbo.SRH_THV
SUM(SysproCompanyA.dbo.SRH
FROM SysproCompanyA.dbo.SRH_THV
dbo.Top50Suppliers ON SysproCompanyA.dbo.SRH_THV
dbo.tblCalendar
WHERE SysproCompanyA.dbo.SRH_THV
and DATEADD(day,-DAY(getdate()
GROUP BY SysproCompanyA.dbo.SRH_THV
ORDER BY dbo.tblCalendar.Year, dbo.tblCalendar.MonthNo
You're missing a ) at the end of the WHERE line. Or, alternately, remove the ( at the beginning of the WHERE.
ASKER
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.
Error in list of function arguments: 'AND' not recognized.
Error in list of function arguments: 'GROUP' not recognized.
Unable to parse query text.
pratima's code needs a ) at the end of the first dateadd line.
ASKER
Before the AND statement? Tried it, still errors.
Just use the one you posted and either remove the ( after WHERE, or place another ) before the GROUP
ASKER
Cluskitt, I did, it gives the following...
'SysproCompanyA.dbo.SRH_TH V2.DAY is not a recognised DateADD option
'SysproCompanyA.dbo.SRH_TH
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_THV 2.DAY in the whole query, let alone on the DATEADD function.
Are those tables or views/procedures you're working with?
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_THV
Are those tables or views/procedures you're working with?
ASKER
Yes i agree the errors are slightly odd. Ok changed the where clause to...
WHERE (SysproCompanyA.dbo.SRH_TH V2.ActualD eliveryDat e > '2012-01-01')
and it runs fine.
SysproCompanyA.dbo.SRH_THV 2 is a view, the other 2 are tables.
WHERE (SysproCompanyA.dbo.SRH_TH
and it runs fine.
SysproCompanyA.dbo.SRH_THV
ASKER
SysproCompanyA.dbo.SRH_THV 2 does have DAY in there but its not being linked to or used in any way
So, what happens if you replace:
> '2012-01-01'
-note that I left the ) in there- with:
BETWEEN DATEADD(year,-1,DATEADD(da y,1-DAY(GE TDATE()),G ETDATE())) AND DATEADD(day,-DAY(GETDATE() ),GETDATE( ))
> '2012-01-01'
-note that I left the ) in there- with:
BETWEEN DATEADD(year,-1,DATEADD(da
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'
ASKER
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...
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
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.
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.
ASKER
It complained about the year as well, changed that to a Y.
It now has no errors but doesnt return any records.
It now has no errors but doesnt return any records.
ASKER
Its defo the where clause, if i change to:
WHERE (SysproCompanyA.dbo.SRH_TH V2.ActualD eliveryDat e 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.
WHERE (SysproCompanyA.dbo.SRH_TH
we get results, obv some of these results would fall within the where clause that we are trying to achieve.
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
This page explains in detail:
http://msdn.microsoft.com/en-us/library/ms186819.aspx
ASKER
Lovely, thanks alot.
ASKER