CODEWITHSUNDEEP
sqlsql-servert-sql
Vinod Kumar
Vinod Kumar

Reputation: 408

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

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,

enter image description here

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

Upvotes: 0

Views: 10541

Answers (5)

Arulkumar
Arulkumar

Reputation: 13237

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

Upvotes: 1

Arjun
Arjun

Reputation: 1

You can try:

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

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

just convert getdate() to date

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

Upvotes: 0

Egan Wolf
Egan Wolf

Reputation: 3573

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

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

you can use <= instead < like below - 

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

Upvotes: 1

Related Questions