comparing dates in sql server 2008

I forgot to notify that I am using SQL SERVER 2008

If you are expecting that the current date is before the expiration dates, then those dates should go second; otherwise, your datediff will be negative. That might be your problem.

i guess i'm not explaining myself clearly, please see the image and it'll explain what i'm looking for.
sql-problem-in-a-diagram.jpg

Did you run what I posted? I think it should clarify some things. You are looking for date differences between 0 and 45 days; however, are getting date differences in reverse, so you will only capture records in your current query when expiration date is today or up to 45 days in the past. It will exclude ALL future dates, passing those to the ELSE condition.

So therefore, my comment above shows you the right way to do it via an example date.

If you ONLY want to see rows that are within 45 days, then you need to add a WHERE clause and filter on date range for those fields.

Hi mwvisa1,
I need to use the query inside my webpage and don't think i can use

DECLARE @DLExpire DATETIME;
SET @DLExpire = DATEADD(day, 30, GETDATE())

Select allOpen in new window

inside my webpage. Is there a way i can generate a result, which shows 45 days ahead that licenses and evaluation period is going to expire and if the date passes still tells me that its has expired.

That code was for you to test what I was saying in SQL Management Studio and is not meant to be your new code for your web page.

My point is that the parameters in DATEDIFF() are reversed; therefore, take your code and flip them.

SELECT DISTINCT REF, FullNameOfAide AS EMP, 'Aide' AS Type, DLExpire
     , PLExpire, M3Eval, DATEDIFF(day, GetDate(), DLExpire) as DL
     , BGDLE=CASE 
          WHEN DATEDIFF(day, GetDate(), DLExpire) BETWEEN 0 AND 45
             THEN 'R'
          ELSE 'W'
       END
     , DATEDIFF(day, GetDate(), PLExpire) as PL
     , BGPLE=CASE
          WHEN DATEDIFF(day, GetDate(), PLExpire) BETWEEN 0 AND 45
             THEN 'R'
          ELSE 'W'
       END
     , DATEDIFF(day, GetDate(), M3Eval) as EVL
     , BG3E=CASE
          WHEN DATEDIFF(day, GetDate(), M3Eval) BETWEEN 0 AND 45
             THEN 'R'
          ELSE 'W'
       END
FROM Aide
;

Select allOpen in new window

I ran both codes and getting more accurate using the old code but the only problem in it, is its not controlling the 45 days ahead part.
I also ran your code too, you can check the dates its showing W for 2012-09-15 and 2011-06-02.
yourcode.jpg
oldcode.jpg

With regard to more accurate, it is a matter of perspective. I don't know which letter you are looking for to mean what, so I will trust you on that...it appears wrong to me if you are looking for dates in the FUTURE to be within 45 days of today to get the results as a negative number then turn around and change that the difference is >= 0 and <= 45. That seems inconsistent. Anyway, if you are expecting this to be a filtered result set, then as I said you have to add a WHERE clause. Given I have no idea which is the correct set of date differences you are looking for you will have to try it yourself first and post what you are trying if it doesn't work...that way I will know what you consider to be the date range.

Based on the way I would think of this:
WHERE (DLExpire >= GETDATE() AND DLExpire <= DATEADD(day, 45, GETDATE()))
OR (PLExpire >= GETDATE() AND PLExpire <= DATEADD(day, 45, GETDATE()))
OR (M3Eval >= GETDATE() AND M3Eval <= DATEADD(day, 45, GETDATE()))

HI mwvisa1,
Thanks for being patient with me and I do appreciate all your help and efforts. I am new in these things, so that's why i posted the table screenshot to give the idea what kind of data in it and what result i am looking for. I am totally aware that, i wasn't clear in my question. So I again apoligize for it. anyway, i used this tsql statement and looks like it has solved the 45 days issue.

SELECT  DISTINCT REF, FullNameOfAide AS EMP, 'Aide' AS Type, DLExpire, PLExpire, M3Eval,

DATEDIFF(day, DLExpire, GetDate()) as DL,
                BGDLE=CASE 
                WHEN DATEADD(day, DATEDIFF(day, DLExpire, GetDate()), 45)<= 45 
                AND DATEADD(day, DATEDIFF(day, DLExpire, GetDate()), 45) < 0 THEN 'W'
                ELSE 'R'
                END,     
                
DATEDIFF(day, PLExpire, GetDate()) as PL,           
                BGPLE=CASE
                WHEN DATEADD(day, DATEDIFF(day, PLExpire, GetDate()), 45)<= 45 
                AND DATEADD(day, DATEDIFF(day, PLExpire, GetDate()), 45) < 0 THEN 'W'
                ELSE 'R'
                END,
                
DATEDIFF(day, M3Eval, GetDate()) as EVL,
                BG3E=CASE
                WHEN DATEADD(day, DATEDIFF(day, M3Eval, GetDate()), 45)<= 45 
                AND DATEADD(day, DATEDIFF(day, M3Eval, GetDate()), 45) < 0 THEN 'W'
                ELSE 'R'
                END
FROM Aide

Select allOpen in new window

i am also attaching the result screenshot for review. I also changed the dates to check if they are working with tsql code or not.
result01.jpg

You are fine. What is difficult is that I believe I am answering your question. Posting the data is great, but it doesn't answer for me what I am suggesting wrong. It only shows me that all your values are coming out negative and I am not sure what that means to you. When you say NOTIFY, I don't know if that means you want the 'W' or 'R' to show up, or do you not want certain rows to show up, or ... something else.

If the 'W' and 'R' displays are not correct, then you need to examine the CASE WHEN statement and the DATEDIFF.

For not wanting rows to show up, that means you need a WHERE clause. See above.

If something else is for example actually sending you an e-mail notification, then you will need to create a SQL job that runs on a schedule that checks your table and if there are any records within their due date send you a message.

If something else is something else, please clarify. :)

Kevin

your postings help me alot to solve this issue, and i admit without your input i won't be able to solve this puzzle as i'm not very good to think outside the box..again thanks for all your help.

working like charm

You are welcome. Sorry it took so long to find the right way to explain it. :)
Communication via e-mail can be very difficult. The important part is it is working for you now.

Best regards and happy coding,
Kevin