CODEWITHSUNDEEP
sql-server
Amir M
Amir M

Reputation: 354

Checking a date between two months sql server

I need to check if the date I have is (for example) between 10th of march and 20th of april and it should work on any year. I'm really new to sql server, I found really good discussions on comparing dates, but they all include years and I don't want to hard-code the year. How this should be done in a neat way?

Upvotes: 4

Views: 6459

Answers (4)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

You could use following approach which generates StartDateTime - StopDateTime ranges for every year from Sales.SalesOrderHeader.OrderDate column (I've used AdventureWorks sample database: SQL2008+). This approach is SARG-able and with an index on OrderDate column the execution plan for the last SELECT statement will include an Index Seek:

CREATE INDEX IN_SalesOrderHeader_OrderDate
ON Sales.SalesOrderHeader(OrderDate);
GO

DECLARE @StartDay TINYINT, 
    @StartMonth TINYINT,
    @StopDay TINYINT, 
    @StopMonth TINYINT;

SELECT @StartDay = 10,
    @StartMonth = 3,
    @StopDay = 20,
    @StopMonth = 4;

DECLARE @FirstYear SMALLINT, @LastYear SMALLINT;
SELECT  TOP(1) @FirstYear = YEAR(h.OrderDate)
FROM    Sales.SalesOrderHeader h    
WHERE   h.OrderDate IS NOT NULL
ORDER BY h.OrderDate ASC;   

SELECT  TOP(1) @LastYear = YEAR(h.OrderDate)
FROM    Sales.SalesOrderHeader h    
WHERE   h.OrderDate IS NOT NULL
ORDER BY h.OrderDate DESC;  

WITH N10(Num) 
AS 
(
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL 
    SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10  
), N100(Num)
AS
(
    SELECT  (a.Num - 1)*10 + b.Num
    FROM    N10 a CROSS JOIN N10 b
)
SELECT  COUNT(z.SalesOrderID) AS SalesOrderCount
FROM 
(
    SELECT  x.CurrentYear, 
        StartDateTime   = DATEADD(DAY, @StartDay-1, DATEADD(MONTH, @StartMonth-1, DATEADD(YEAR, x.CurrentYear - 1900, 0))),
        StopDateTime    = DATEADD(DAY, @StopDay/*-1*/, DATEADD(MONTH, @StopMonth-1, DATEADD(YEAR, x.CurrentYear - 1900, 0)))
    FROM
    (
        SELECT  @FirstYear + n.Num - 1 AS CurrentYear
        FROM    N100 n
        WHERE   n.Num <= (@LastYear - @FirstYear + 1)
    ) x
) y
CROSS APPLY 
(
    SELECT  h.SalesOrderID
    FROM    Sales.SalesOrderHeader h
    WHERE   h.OrderDate >= y.StartDateTime
    AND     h.OrderDate < y.StopDateTime
) z

The execution plan:

enter image description here

Note: this solution assumes that Sales.SalesOrderHeader.OrderDate column has maximum 100 years.

Upvotes: 0

Obsidian Phoenix
Obsidian Phoenix

Reputation: 4155

You can extract parts of the date DAY, MONTH and YEAR respectively. For example

Select MONTH(GETDATE())

Will give you the Month Number for the current date.

If you want to check if a month is between certain values before doing an insert:

If Exists (Select 'x' Where Month(GETDATE()) Between 3 and 4 --March to April
Begin
  --Do work here
End

If you want to get all values from a table where the CreatedDate is between two values:

Select *
From MyTable
Where MONTH(CreatedDate) Between 3 and 4 -- March to April

Finally, to check if CreatedDate is between two specific dates in a year (but any year), you could do the below.

Select *
From MyTable
Where CreatedDate Between Convert(Varchar, YEAR(CreatedDate)) + '-03-12' 
        and Convert(Varchar, YEAR(CreatedDate)) + '-03-15'

Upvotes: 5

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

There's probably a shorter way, but you could do

SELECT  *
FROM your table
--this will transform your date in a varchar with 2 digits for month and 2 digits for day
WHERE  RIGHT('0' + RTRIM(CAST (MONTH(<yourdatefield>) as varchar(2))), 2) + 
       RIGHT('0' + RTRIM(cast(DAY(<yourdatefield>) as varchar(2))), 2)

BETWEEN '0310' and '0410'

see SqlFiddle

Upvotes: 1

theRealNG
theRealNG

Reputation: 1

Please refer to the following link for EXTRACT function which helps you get only the month from a date: http://www.w3schools.com/sql/func_extract.asp

Upvotes: -1

Related Questions