Truncate value in expression and compare to current date

[scorpionpta]

Code:

 

Post Edited by scorpionpta at 08/22/2011 10:35

[cbarlow3]

I think there are several issues you'll need to decide on:  first, what if that LAST_LOGIN_TIME is null?  Do you only want people who have logged on, but longer than 90 days ago, or do you really all people who have not logged on in the past 90 days, including those who have never logged on at all?  If the latter, you'll need to interpret a null in this field as being a valid reason to be on the report.  Since that's a little trickier, I'll assume that's what you want...it will be easy to change my example to work the other way.  Secondly, what to do about time zones, and what does 90 days really mean, etc.  Assuming you run this report more often than every 90 days, and assuming there's nothing horrendous about someone falling on one side or the other of this cutoff by a few hours, the definition and timezone treatment shouldn't matter too much.  I'm assuming that "90 days" is a bit arbitrary and that if someone misses the cutoff a bit one way or the other, that it's okay.  Another issue is where you get today's date from.  Is there an environment sort of variable in your database that tells you what "today" is, or do you want to use the operating system date/time?  I'll assume operating system date/time for now.

To list all people who do NOT have a LAST_LOGIN_TIME that has a date of Today - 90 days or later, here's how the SQL query might look:

SELECT
  USER.USERNAME AS USERNAME,
  CASE
    WHEN USER.LAST_LOGIN_TIME IS NULL THEN NULL
    ELSE DATE(USER.LAST_LOGIN_TIME)
  END AS LAST_LOGIN_DATE
FROM
  USER
WHERE
  LAST_LOGIN_TIME IS NULL OR
  DAYS(CURRENT_DATE) - DAYS(LAST_LOGIN_TIME) > 90

The exact syntax for current date and date subtraction depends on your version of SQL.  I think CURRENT_DATE works for DB2, MySQL, PostgreSQL, while Oracle uses sysdate, and SQL Server uses getdate().  For date subtraction, I've shown the DB2 syntax DAYS(laterDate) - DAYS(earlierDate).  I think Oracle and PostgreSQL let you just subtract two dates directly, while MySQL and SQLServer use a DATEDIFF function, but the syntax varies slightly:  MySQL is DATEDIFF(laterDate, earlierDate) and SQL Server is DATEDIFF(day, earlierDate, laterDate)  where "day" is an actual literal to indicate what flavor of different you're asking for.

Hope that helps!

Carl