Improve company productivity with a Business Account.Sign Up

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

Rolling Date Range

'morning. SQl 2k5. I need to modify my access query into an sp. i have started it, but am having issues with the date range. i need the range to be current month (partial or full) and prior 11 months. For example, today's report should reference: 5/01/06 to 4/09/07. Here is what I have:

(I got the date part from a prior post, but it isn't working as a rolling 12 month. It is returning Jun 2006 thru March 2007.)

ALTER PROCEDURE [dbo].[upOrders_WrntyExchsCY] AS
DECLARE @d datetime
SET @d = CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)
SET @d = dateadd(day, 1- datepart(day, @d ), @d )
SELECT ord.ServiceType, ord.OrderType, ord.ReportMonth,
CONVERT(CHAR(8), lmd.Report_M_Date, 10) AS Report_M_Date
FROM dbo.tblOrders AS ord LEFT OUTER JOIN dbo.tblLU_RptMDate AS lmd ON
ord.ReportMonth = lmd.ReportMonth WHERE (ord.OrderType <> 'PO') AND
(ord.OrderType <> 'SP') AND (lmd.Report_M_Date >= dateadd(month, -11, @d ))



0
adraughn
Asked:
adraughn
  • 6
  • 6
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try

 AND (lmd.Report_M_Date >= dateadd('m', -11, @d ))
0
 
adraughnAuthor Commented:
that returned an error.

Invalid parameter specified for DateAdd (maybe because you are now passing 'm' as a string instead of month?)
0
 
Rey Obrero (Capricorn1)Commented:
adraughn,
sorry about that, thinking it was on access.
does SQL accept dateserial ?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
adraughnAuthor Commented:
i don't believe so, but i could be wrong. i have only used it in vb.

adria
0
 
Rey Obrero (Capricorn1)Commented:
is Report_M_Date in the same format as @d ?
0
 
adraughnAuthor Commented:
yes, they are both dates.
0
 
Rey Obrero (Capricorn1)Commented:
that is not what i meant
mm/dd/yy is a date format
mm/dd/yyyy is another date format

are they both in this format  mm/dd/yy  ?
0
 
adraughnAuthor Commented:
not exactly, but i don't see why that matters, because the calcualtion in SQLwill use the date field, no matter how you format it.
0
 
adraughnAuthor Commented:
I ended up solvign it with a simple view instead of the stored procedure.

Here is the view:

SELECT     ord.ServiceType, ord.OrderType, ord.ReportMonth, CONVERT(CHAR(8), lmd.Report_M_Date, 10) AS Report_M_Date
FROM         dbo.tblOrders AS ord LEFT OUTER JOIN
                      dbo.tblLU_RptMDate AS lmd ON ord.ReportMonth = lmd.ReportMonth
WHERE     (ord.OrderType <> 'PO') AND (ord.OrderType <> 'SP') AND (lmd.Report_M_Date >= DATEADD(month, - 12, GETDATE()))
0
 
Rey Obrero (Capricorn1)Commented:
In Access results varies if one of criteria in a query is a datefield and the datefield is not in the same format as the criteria.

see this thread
http://www.experts-exchange.com/Database/Miscellaneous/Q_22496839.html

and the selected answer

http://www.experts-exchange.com/Database/Miscellaneous/Q_22496839.html?cid=236#a18866018

try using the same format in both sides.
0
 
Rey Obrero (Capricorn1)Commented:
well, thats good
0
 
adraughnAuthor Commented:
this was all in SQL 2k5 - created for an access FE so the formatting issue does not apply. Thanks anyway for your resposnes.

adria
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now