• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

comparing dates in sql server 2008

Hi,
I am confused with one of my sql query. I've a situation where users input there licenses and other expiry dates. In my case we go with reg. driver's license, professional driver's license and 3 month evaluation expiration dates.

when i run the query, query supposed to notify me before 45 days ahead, that the licenses or 3 month evaluation expiry dates are going to expire. The t-sql i wrote is as under:

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

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

Open in new window


Any comments, feedback or suggestion would be deeply appreciated.

0
s_hausen
Asked:
s_hausen
  • 7
  • 7
1 Solution
 
s_hausenAuthor Commented:
I forgot to notify that I am using SQL SERVER 2008
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
To illustrate this, take a look at this:
DECLARE @DLExpire DATETIME;
SET @DLExpire = DATEADD(day, 30, GETDATE())

SELECT @DLExpire, DL=DATEDIFF(day, @DLExpire, GetDate())
     , BGDLE=CASE WHEN DATEDIFF(day, @DLExpire, GetDate()) BETWEEN 0 AND 45 THEN 'R' ELSE 'W' END
	 , DL2=DATEDIFF(day, GetDate(), @DLExpire)
	 , BGDLE2=CASE WHEN DATEDIFF(day, GetDate(), @DLExpire) BETWEEN 0 AND 45 THEN 'R' ELSE 'W' END
;

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
s_hausenAuthor Commented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
s_hausenAuthor Commented:
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())

Open 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.
0
 
Kevin CrossChief Technology OfficerCommented:
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
;

Open in new window

0
 
s_hausenAuthor Commented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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()))
0
 
s_hausenAuthor Commented:
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

Open 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
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
 
s_hausenAuthor Commented:
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.
0
 
s_hausenAuthor Commented:
working like charm
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now