DATEDIFF FUNCTION IN SQL SERVER

DATEDIFF: The DATEDIFF function returns the difference between two date values or returns the time between two dates in sql server.

Now, we will calculate Years,Month,Days,Hours,Minute,Second etc by using DATEDIFF function.

DECLARE @DATE1 DATETIME = GETDATE()

DECLARE @DATE2 DATETIME = '2020-08-15'

SELECT @DATE1 AS DATE1,  @DATE2 AS DATE2

CALCULATE YEAR:

SELECT DATEDIFF(YEAR,@DATE1,@DATE2) AS CALULATED_YEAR

CALCULATE MONTH:

SELECT DATEDIFF(MONTH,@DATE1,@DATE2) AS CALULATED_MONTH

CALCULATE DAY:

SELECT DATEDIFF(DAY,@DATE1,@DATE2) AS CALULATED_DAY

CALCULATE HOUR:

SELECT DATEDIFF(HOUR,@DATE1,@DATE2) AS CALULATED_HOUR 

CALCULATE MINUTE:

SELECT DATEDIFF(MINUTE,@DATE1,@DATE2) AS CALULATED_MINUTE

CALCULATE SECOND:

SELECT DATEDIFF(SECOND,@DATE1,@DATE2) AS CALULATED_SECOND

You can see the below screenshot for further clarifications,

              - - - - - - Cheers, Happy to Help! - - - - - - 

JOINS IN SQL SERVER

JOINS: The joins are used to get or retrieve records from more than one tables.It combines rows from two or more tables based on a common column between them. The four types of joins are used. These are:
a) LEFT JOIN
b) INNER JOIN
c) RIGHT JOIN
d) FULL JOIN

a) LEFT JOIN: The LEFT JOIN is also known as LEFT OUTER JOIN. When we use left join then It fetches all records from the left table and the matched records from the right table. And if there is no matching row in the right side table then the result shows NULL. The syntax of Left join is as:

     SELECT 
          COLUMN1(ID), COLUMN2 
     FROM 
          TABLE 1 
     LEFT JOIN 
          TABLE2 
     ON 
          TABLE1.COLUMN1(ID) = TABLE2.COLUMN1(ID)

The TABLE1 is a left side table and TABLE2 is a right side table.

b) INNER JOIN: The INNER JOIN is also known as EQUI JOIN. When we use inner join then it fetches all records that have matched records in two or more tables. The syntax of inner join is as:

      SELECT 
          COLUMN1(ID), COLUMN2 
      FROM 
          TABLE 1 
      JOIN or INNER JOIN
          TABLE2 
      ON 
          TABLE1.COLUMN1(ID) = TABLE2.COLUMN1(ID)

c) RIGHT JOIN: The RIGHT JOIN is also known as RIGHT OUTER JOIN. When we use right join then It fetches all records from the right table and the matched records from the left table. And if there is no matching row in the left side table then the result shows NULL.The right join or right outer join is similar to left join. The syntax of right join is as:

       SELECT 
            COLUMN1(ID), COLUMN2 
       FROM 
           TABLE 1 
       RIGHT JOIN 
           TABLE2 
      ON 
           TABLE1.COLUMN1(ID) = TABLE2.COLUMN1(ID)

The TABLE1 is a left side table and TABLE2 is a right side table.

d) FULL JOIN: The FULL JOIN is also known as FULL OUTER JOIN. When we use full join then It fetches all records from the left table and right table.And if there is no matching row in the right side table then the result shows NULL The syntax of full join is as:

      SELECT 
          COLUMN1(ID), COLUMN2 
      FROM 
          TABLE 1 
      FULL JOIN or FULL OUTER JOIN
          TABLE2 
      ON 
          TABLE1.COLUMN1(ID) = TABLE2.COLUMN1(ID)

The TABLE1 is a left side table and TABLE2 is a right side table.

            - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO USE SUB-QUERY IN SQL SERVER

A subquery is a query that are used nested inside into the another statements i.e select,insert,update,delete sql statements. A subquery is also known as inner query or nested query.

A subquery can be used in FROM Clause,WHERE Clause and SELECT Clause.

So, here we will use a subquery into the WHERE Clause.

First, we will create a product table and customer table and insert records into it. Please see the created Product table and Customer table.

Product Table:

Customer Table:

Now, we will use a subquery into the Where clause. The syntax is:

       SELECT 
ID,
PRODUCT_NAME,
PRODUCT_CODE,
PRICE 
FROM 
Product 
WHERE 
CUSTOMER_ID = (SELECT 
                                                 ID 
                                         FROM 
                                                 CUSTOMER 
                                         WHERE 
                                                 ID = 2)

Also, you can see the screenshot for further clarifications.

 
                     - - - - - - Cheers, Happy to Help! - - - - - - 

DATA TYPE RANGES IN SQL SERVER

Data Type   :   bigint
Range         :   -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
Storage       :   8 Bytes

Data Type   :   int
Range         :   -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
Storage       :   4 Bytes

Data Type   :   smallint
Range         :    -2^15 (-32,768) to 2^15-1 (32,767)
Storage       :    2 Bytes

Data Type   :   tinyint
Range         :   0 to 255
Storage       :   1 Byte

Data Type   :   bit
Range         :   0 to 1
Storage       :   1 Byte

Data Type   :   decimal
Range         :   -10^38+1 to 10^381-1
Storage      :   5 to 17 Bytes

Data Type   :   numeric
Range         :   -10^38+1 to 10^381-1
Storage       :   5 to 17 Bytes

Data Type   :   money
Range         :   -922,337,203,685,477.5508 to 922,337,203,685,477.5807
Storage       :   8 Bytes

Data Type   :   smallmoney
Range         :   -214,478.3648 to 214,478.3647
Storage       :   4 Bytes

Data Type   :   real
Range         :   -3.40E+38 to 3.40E+38
Storage       :   4 Bytes

Data Type   :   float
Range         :   1.79E+308 to 1.79E+308
Storage       :   It depends on the value(n)

Data Type   :   char
Range         :   0 chars to 8000 chars
Storage       :   n bytes

Data Type   :   varchar
Range         :   0 chars to 8000 chars
Storage       :   n bytes + 2 bytes

Data Type   :   varchar(max)
Range         :   0 chars to 2^31 chars
Storage       :   n bytes + 2 bytes

Data Type   :   text
Range         :   0 chars to 2,147,483,647 chars
Storage       :   n bytes + 4 bytes

Data Type   :   nchar
Range         :   0 chars to 4000 chars
Storage       :   2 times n bytes

Data Type   :   nvarchar
Range         :   0 chars to 4000 chars
Storage       :   2 times n bytes + 2 bytes

Data Type   :   ntext
Range         :   0 chars to 1,073,741,823 chars
Storage       :   2 times the string length

Data Type   :   datetime
Range         :   1753-01-01 to 9999-12-31
Storage       :   8 bytes

Data Type   :   smalldatetime
Range         :   1900-01-01 to 2079-06-06
Storage       :   4 Bytes, Fixed

Data Type   :   date
Range         :   0001-01-01 to 9999-12-31
Storage       :   3 Bytes, Fixed

Data Type   :   time
Range         :   00:00:00.0000000 to 23:59:59.9999999
Storage       :   5 Bytes

Data Type   :   datetimeoffset
Range         :   0001-01-01 to 9999-12-31
Storage       :   10 Bytes

Data Type   :   datetime2
Range         :   0001-01-01 to 9999-12-31
Storage       :   6 Bytes

Data Type   :   binary
Range         :   0 Bytes to 8000 Bytes
Storage       :   n Bytes

Data Type   :   varbinary
Range         :   0 Bytes to 8000 Bytes
Storage       :   The actual length of string + 2 Bytes

Data Type   :   image
Range         :   0 Bytes to 2,147,483,647 Bytes

                         - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO CREATE A FUNCTION AND USE IT INTO THE STORED PROCEDURE IN SQL SERVER

The function is a sql statements that returns a value. So here,we will create one scalar function and use it into the Stored Procedure in sql server. The process is as follows:

Step 1: Open the database that you created in your system.

Step 2: Click on the Programmability > Functions then you will see the Scalar-valued Functions option and right click on it then you will see an option i.e. New Scalar-valued Function. Click on it. Please see the below screenshot.

After click on the New Scalar-valued Function, you will see new window on where you can write your functions or sql statements.

Here, we will create a function to get the employee salary by passing employee id into the function. Please see the below screenshots.

Now, we will use the above function into the stored procedure.

So, here we have successfully created a new stored procedure and used above function into it by pass employee id. So, now we will see that how we will execute our stored procedure and get salary from the above created function. Please see the below screenshot:

                       - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO CREATE A STORED PROCEDURE IN SQL SERVER

Stored Procedure is a sql statements that you can write your own logic with sql commands or modify an existing stored procedures. It is a pre-compiled format and it also improves the performance while accessing data from database. Stored procedures are cached and can be reused. So here we will see that how we can create a new stored procedure in sql server. The process is as follows:

Step 1: Open the database that you created in your system.

Step 2: Click on the Programmability menu then you will see the Stored Procedure option and right click on it then you will see an option i.e. Stored Procedure. Please see the below screenshot.

After click on stored procedure, you will go to a new query window on where you can write sql statements or logic. Please see the below screenshot.

So, now you will create a procedure to add a new employee into an Employee table. Please see it below.

When you will be done with your changes in procedure then you have to execute a complete query at once by pressing F5. After that, your procedure would be create successfully.

                                  - - - - - - Cheers, Happy to Help! - - - - - - 

HOW TO INSERT, SELECT, UPDATE AND DELETE RECORDS IN SQL SERVER

First, we will create a new table i.e. Employee with columns as "ID", "EMP_NAME", "EMP_CODE", "DEPARTMENT", "SALARY", "CREATED_DATE_TIME", "IS_DELETED". Please see the below screenshot.

The table Employee has been created. Now we will do a operations i.e.
a) INSERT A NEW RECORD IN EMPLOYEE TABLE
b) FETCH RECORDS FROM EMPLOYEE TABLE BY SELECT
c) UPDATE AN EXISTING RECORD IN EMPLOYEE TABLE
d) DELETE A RECORD FROM EMPLOYEE TABLE

a) INSERT NEW RECORD IN EMPLOYEE TABLE: We are inserting a new record into the table by using insert command. The syntax is as follows:

INSERT INTO EMPLOYEE(EMP_NAME,EMP_CODE,DEPARTMENT,SALARY,CREATED_DATE_TIME) VALUES('RAMAN SACHDEVA','EMP_001','DEVELOPMENT',10000,GETDATE()) 
This query is used into the below screenshot. Please have a look into this.

The 1 row afftected. It means one record has been inserted in Employee table.

b) FETCH RECORDS FROM TABLE BY SELECT: After inserting a new record, you will get or fetch a record from Employee table by using Select command. The syntax is as follows:

SELECT * FROM EMPLOYEE

 c) UPDATE AN EXISTING RECORDING IN EMPLOYEE TABLE: When record has been inserted into the table then we have to do some updation for some records. In this case, we are using the Update command to update an existing record. The syntax is as follows:

UPDATE EMPLOYEE SET EMP_NAME = 'RMN SACHDEVA',  WHERE ID = 1

d) DELETE A RECORD FROM EMPLOYEE TABLE: We can delete a record from table by using Delete command. Also we can delete a record from table in two ways:
1) We can delete a record from table as soft deleting process or updating only a IS_DELETED column by using update command. The syntax is:

UPDATE EMPLOYEE SET IS_DELETED = 1 WHERE ID = 1

You will see into the above screenshot that the IS_DELETED column has been updated with 1. 1 means soft delete from table. We can active same record by updated value 0 in IS_DELETED column.

2) We can delete a record permanently from table by using delete command. The syntax is:
DELETE FROM EMPLOYEE WHERE ID = 1

You will see into the above screenshot that an existing records has been deleted permanently from Employee table.

                                   - - - - - - Cheers, Happy to Help! - - - - - -

               

HOW TO INSERT RECORDS IN TEMPORARY TABLE IN SQL SERVER

Step 1 - You have to first create Temporary table.

DECLARE @USERTEMPTABLE TABLE
(
   ID INT PRIMARY KEY IDENTITY(1,1),

   NAME NVARCHAR(100),

   USER_NAME NVARCHAR(200),

   EMAIL NVARCHAR(200),

   MOBILE_NUMBER NVARCHAR(50),

   ADDRESS NVARCHAR(200)
)

Step 2 - Insert records into the temporary table.

INSERT INTO @USERTEMPTABLE(NAME,USER_NAME,EMAIL,MOBILE_NUMBER,ADDRESS)
VALUES
('RAMAN SACHDEVA','RMN_001','***@gmail.com','98780*****','CHANDIGARH'),
('ANAND SACHDEVA','ANAD_051','***@gmail.com','99520*****','CHANDIGARH'),
('ROHIT SACHDEVA','RHT_250','***@gmail.com','87520*****','CHANDIGARH'),
('ARUN SHARMA','ARN_002','***@gmail.com','95140*****','MOHALI'),
('SURINDER SINGH','SRN_300','***@gmail.com','98520*****','JALANDHAR'),
('KULWINDER SINGH','KLN_1000','***@gmail.com','87550*****','AMRITSAR'),
('PALKA SACHDEVA','PLK_550','***@gmail.com','98560*****','CHANDIGARH'),
('AKASHDEEP SINGH','AKSH_121','***@gmail.com','79851*****','DELHI'),
('HARISH KUMAR','HRSH_050','***@gmail.com','79862*****','MUMBAI'),
('FATEH SINGH','FTH_660','***@gmail.com','96468*****','PUNE')

After inserting the records, you can fetch all the records from temporary table with the below query.

SELECT * FROM @USERTEMPTABLE

Here is an example of inserting a records into the temorary table. Please see the screenshot for further clarification.

- - - - - - - - - - - - - - - - - - - - - - - - Cheers, Happy to Help! - - - - - - - - - - - - - - - - - - - - - - - - - - -

DATE-TIME FORMATS IN SQL SERVER

DIFFERENT DATETIME FORMATS WITH CURRENT DATETIME IN SQL SERVER.

For example the current date time is :

SELECT GETDATE() AS CURRENT_DATE_TIME 

CURRENT_DATE_TIME: "2020-07-06 23:17:10.490"

SELECT CONVERT(VARCHAR(10),GETDATE(),101) AS DATE1  

-- DATE1 : "07/06/2020"

SELECT CONVERT(VARCHAR(10),GETDATE(),102) AS DATE2  

-- DATE2 : "2020.07.06"

SELECT CONVERT(VARCHAR(10),GETDATE(),103) AS DATE3  

-- DATE3 : "06/07/2020"

SELECT CONVERT(VARCHAR(10),GETDATE(),104) AS DATE4  

-- DATE4 : "06.07.2020"

SELECT CONVERT(VARCHAR(10),GETDATE(),105) AS DATE5  

-- DATE5 : "06-07-2020"

SELECT CONVERT(VARCHAR(11),GETDATE(),106) AS DATE6  

-- DATE6 : "06 Jul 2020"

SELECT CONVERT(VARCHAR(12),GETDATE(),107) AS DATE7  

-- DATE7 : "Jul 06, 2020"

SELECT CONVERT(VARCHAR(12),GETDATE(),108) AS DATE8  

-- DATE8 : "23:13:55"

SELECT CONVERT(VARCHAR(11),GETDATE(),109) AS DATE9 

 -- DATE9 : "Jul  6 2020"

SELECT CONVERT(VARCHAR(10),GETDATE(),110) AS DATE10  

-- DATE10 : "07-06-2020"

SELECT CONVERT(VARCHAR(10),GETDATE(),111) AS DATE11 

-- DATE11 : "2020/07/06"

SELECT CONVERT(VARCHAR(10),GETDATE(),112) AS DATE12  

-- DATE12 : "20200706"

SELECT CONVERT(VARCHAR(11),GETDATE(),113) AS DATE13  

-- DATE13 : "06 Jul 2020"

SELECT CONVERT(VARCHAR(11),GETDATE(),114) AS DATE14  

-- DATE14 : "23:15:27:50"

Please see the below screenshot for the clarifications.

                                - - - - - - Cheers, Happy to Help! - - - - - -