SQL query to get data till current date(considering month and date) for the particular year

Question

I need an SQL query to calculate data till current date (till current month and date) particular year. I tried following query by it filters the day of previous month too,

You can see that it filters till 11 in Jan, Feb and March. But i want to calculate the whole data for Jan,Feb and till 11th in March

Answer 1

you can use <= instead < like below -

where day(orderdate)<=day(getdate())

Answer 2

Check this one. Generally all you need is all previous months (where days don't matter) and current month with previous days.

where
    (Month([OrderDate]) < MONTH(GETDATE()) OR
        (Month([OrderDate]) = MONTH(GETDATE()) AND DAY([OrderDate]) < DAY(GETDATE())))
    AND YEAR([ORDERDATE]) = 1997

Answer 3

Adding the MONTH and DAY check within the AND operator will works.

Could you please try the following query with the sample data:

DECLARE @Orders TABLE (Id INT IDENTITY(1,1) NOT NULL, OrderDate DATETIME NOT NULL);

INSERT INTO @Orders (OrderDate) VALUES 
('1997-01-01 10:00:00'), ('1997-01-15 10:00:00'), ('1997-01-13 10:00:00'),
('1997-02-02 10:00:00'), ('1997-02-20 10:00:00'), ('1997-02-13 10:00:00'),
(GETDATE()), (DATEADD(DAY, +10, GETDATE()));

SELECT *
FROM @Orders 
WHERE YEAR(OrderDate) = 1997 
      AND (MONTH(ORDERDATE) <= MONTH(GETDATE()) AND DAY(ORDERDATE) < DAY(GETDATE()))

Output will be:

Id  | OrderDate
-----------------------------
1   | 1997-01-01 10:00:00.000
4   | 1997-02-02 10:00:00.000

Answer 4

just convert getdate() to date

where cast(orderdate as date)<= cast(getdate() as date)

Answer 5

You can try:

where  (Month([OrderDate]) < MONTH(GETDATE()) 
   OR (Month([OrderDate]) = MONTH(GETDATE()) 
   AND DAY([OrderDate]) < DAY(GETDATE())))
   AND YEAR([ORDERDATE]) = 1998