Click here to Skip to main content
15,885,546 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi ,

I am trying to check if my start date and end date is as per my scnerio or not

start date should be >= getdate() and enddate <= getdate()

but i am not getting proper records

below is what I have tried so far

SQL
Declare @LoggedUserID int =4

SELECT
   m.*,
   d.DivisionName,
   l.Name LocationName,
   l.Code LocationCode,
   CASE 
      WHEN mr.MarketingUpdateUnReadID IS NULL THEN CAST(1 AS BIT)
      ELSE CAST(0 AS BIT)
   END IsRead
   FROM MarketingUpdate m
      INNER JOIN F_UserDivisions(@LoggedUserID) d on m.DivisionID = d.DivisionID
      INNER JOIN F_UserLocationHierarchy(@LoggedUserID) l on m.LocationID = l.LocationID
      LEFT JOIN MarketingUpdateUnRead mr on m.MarketingUpdateID = mr.MarketingUpdateID AND mr.UserID = @LoggedUserID
   WHERE cast(m.StartDate as date) >= cast(GETDATE() as date)
      AND (m.EndDate IS NULL OR cast(m.EndDate as date) <= cast(GETDATE() as date))
      AND m.IsInActive = 0
Posted
Updated 25-Nov-15 1:06am
v3
Comments
Richard Deeming 25-Nov-15 11:32am    
Are you sure you've got those comparisons the right way round?

You're asking for things that both start on or after today and end on or before today.

Unless you've got some very odd data, the only records which match will be those which start and end today. Anything else would have to end before it started to match your criteria.

Hi,

Try Below query


SQL
WHERE DateDiff(d,@FromDate,GetDate()) >= 0
                 AND DateDiff(d,@ToDate,GetDate())<= 0
 
Share this answer
 
Use BETWEEN:
SQL
... WHERE m.IsInActive AND (m.EndDate IS NULL OR CAST(GETDATE() AS DATE) BETWEEN m.StartDate AND m.EndDate)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900