Query Help Please
-
I need to include in my query a check on a date field. I only want records returned that are from the last six months. I would I go about doing this.
have a startdate and enddate fields.
-
I have not Tweeked this however, i seen that you needed this and this is the type of logic that you are looking for.
This comes from the northwind database that is in your sql2000 files
ALTER procedure
[Sales by Year]
@Beginning_Date
DateTime, @Ending_Date DateTime AS
SELECT
Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM
Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE
Orders.ShippedDate Between @Beginning_Date And @Ending_Date
Dam again!
-
Beware of the times, which are also included. You might want to add one day to the end day if you are missing values.
-
select getdate() --current date
select dateadd(mm,-6,getdate()) -- last 6 months
-
i usually use this
--------------------------
ALTER procedure [Sales by Year]
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year
FROM Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID
WHERE convert(varchar(12),Orders.ShippedDate,103) Between convert(varchar(12),@Beginning_Date,103) And
convert(varchar(12),@Ending_Date,103)
-
What is the 103?
Dam again!
-
Have you tried running this??
Select convert(varchar(12),GetDate(),103)
Check bol for all other possibilities (in convert/dates).
-
103 is an example of an optional parameter that can be passed to the convert function. For more information, see the BOL.
jg
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply