x/y/z columns date is greater than 60 days but less than 210 days.
x/y/z columns date is greater than 60 days between 210 and 330.
During test design, have prepared an excel sheet with the 'DAYS' function to auto calculate the days. However, I need to run SQL SELECT query against few tables copy the data into excel to cross-check, and then use the test data in API endpoints for further validation. Instead of using excel, I got a question why can't I check in SQL itself. Without putting much effort found inbuilt datefunctions, though they were many datefunctions I happened to use the below for testing.
DATEDIFF(datepart, startdate, enddate) - three arguments
datepart - units in which differences to be reported.
SELECT
E.EMPID,
E.DEPT,
E.JOINDATE,
FROM EMPLOYEE E
WHERE DATEDIFF(DAY,E.JOINDATE,GETDATE()) > 60
DATEADD(datepart, number, date) - three arguments
this function adds a specified number value to a specified datepart.
Ex: Notification to be given between 210 and 330 days based on 'y' column date.
SELECT
E.EMPID,
E.DEPT,
E.JOINDATE,
FROM EMPLOYEE E
WHERE E.JOINDATE <= DATEADD(DAY,-210,GETDATE())
AND E.JOINDATE >= DATEADD(DAY,-310,GETDATE())
CONVERT(DATE, CREATEDON) - Though it's not datefunction, I have used it widely to format the DateTime columns to a specific format.
