Querying Microsoft SQL Server 2012

   Study notes for Querying Microsoft SQL Server 2012 in a mind map.

SQL - Datefunctions

        The Other day I was testing a story wherein I need to count the number of days with the current date to validate the end results.

        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.

        

    

Typical Testing Day

 

            Recently I came across a situation where I need to find whether a particular column has an integer value or varchar...there could be many ways to find the outcome however found the below query which is very simple.

Let us say we have a table called EMPLOYEE and ID is a varchar column, I need to find records which as integer or char.

SELECT * FROM EMPLOYEE

                    WHERE ID LIKE '%[^0-9]%' -- Looks for 0-9

                     OR ID LIKE '_%-%' -- Looks for the '-' other than 1st position.