?
Solved

comparing dates in sql server 2008

Posted on 2011-09-14
14
Medium Priority
?
308 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
14 Comments
 

Author Comment

by:s_hausen
ID: 36539515
I forgot to notify that I am using SQL SERVER 2008
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36539789
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 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36539802
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:s_hausen
ID: 36540189
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 60

Expert Comment

by:Kevin Cross
ID: 36540282
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
ID: 36540354
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 60

Expert Comment

by:Kevin Cross
ID: 36540414
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
 

Author Comment

by:s_hausen
ID: 36540445
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 60

Expert Comment

by:Kevin Cross
ID: 36540477
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
ID: 36540505
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 60

Expert Comment

by:Kevin Cross
ID: 36540569
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
ID: 36540627
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
ID: 36540630
working like charm
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36540774
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

777 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