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_WrntyExchs
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 ))
(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_WrntyExchs
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 ))
ASKER
that returned an error.
Invalid parameter specified for DateAdd (maybe because you are now passing 'm' as a string instead of month?)
Invalid parameter specified for DateAdd (maybe because you are now passing 'm' as a string instead of month?)
adraughn,
sorry about that, thinking it was on access.
does SQL accept dateserial ?
sorry about that, thinking it was on access.
does SQL accept dateserial ?
ASKER
i don't believe so, but i could be wrong. i have only used it in vb.
adria
adria
is Report_M_Date in the same format as @d ?
ASKER
yes, they are both dates.
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 ?
mm/dd/yy is a date format
mm/dd/yyyy is another date format
are they both in this format mm/dd/yy ?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
https://www.experts-exchange.com/questions/22496839/Product-Sales-by-Customer-over-date-peroid.html
and the selected answer
https://www.experts-exchange.com/questions/22496839/Product-Sales-by-Customer-over-date-peroid.html?cid=236&anchorAnswerId=18866018#a18866018
try using the same format in both sides.
see this thread
https://www.experts-exchange.com/questions/22496839/Product-Sales-by-Customer-over-date-peroid.html
and the selected answer
https://www.experts-exchange.com/questions/22496839/Product-Sales-by-Customer-over-date-peroid.html?cid=236&anchorAnswerId=18866018#a18866018
try using the same format in both sides.
well, thats good
ASKER
this was all in SQL 2k5 - created for an access FE so the formatting issue does not apply. Thanks anyway for your resposnes.
adria
adria
AND (lmd.Report_M_Date >= dateadd('m', -11, @d ))