Trouble with date selection
-
I'm having trouble with this query, use to work in SQL Server 2008, but in 2012, I'm getting rows returned from various dates So I'm just trying to get all the orders from Dec 3, 2013, I'm passing integer values into the function 12 3 2013 and using parameters Day, Month, Year My Original SQL
"SELECT * From CompletedOrders " & _
"UNION " & _
"SELECT * FROM CompletedOrdersHistory " & _
"WHERE Day(OrderDate)=@TheDay " & _
"AND Month(OrderDate)=@TheMonth " & _
"AND Year(OrderDate)=@TheYear"Not really sure how to write this or what to search on. I have spent about an hour on this searching the internet. [update] I got this to work, but I have to use the greater than, so now I have to back date the date, which will get me trouble at the start of the month, guess I'll try looking up removing a day in SQL
DECLARE @TheDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;set @month = 12;
set @day = 2;
set @year = 2013;
set @TheDate = DateFromParts(@year, @Month, @Day);SELECT * From CompletedOrders
WHERE OrderDate > @TheDate
UNION
SELECT * FROM CompletedOrdersHistory
WHERE OrderDate > @TheDate;Well this is what I ended up with. It works, not sure of efficient it is
DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;
DECLARE @month AS INTEGER;
DECLARE @day AS INTEGER;
DECLARE @year AS INTEGER;set @month = 12;
set @day = 3;
set @year = 2013;
set @startDate = DATETIMEFROMPARTS(@year, @Month, @Day, 0, 0, 0, 0);
set @stopDate = DATETIMEFROMPARTS(@year, @Month, @Day, 23, 59, 59, 999);SELECT * From CompletedOrders
WHERE OrderDate > @startDate AND OrderDate < @stopDate
UNION
SELECT * FROM CompletedOrdersHistory
WHERE OrderDate > @startDate AND OrderDate