Solved

comparing dates in sql server 2008

Posted on 2011-09-14
14
267 Views
Last Modified: 2012-06-27
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
Comment
Question by:s_hausen
  • 7
  • 7
14 Comments
 

Author Comment

by:s_hausen
Comment Utility
I forgot to notify that I am using SQL SERVER 2008
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
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
 

Author Comment

by:s_hausen
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:s_hausen
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:s_hausen
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:s_hausen
Comment Utility
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
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
 

Author Comment

by:s_hausen
Comment Utility
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
 

Author Closing Comment

by:s_hausen
Comment Utility
working like charm
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now