In this article I will explain with an example, what is DATEFORMAT command in SQL Server, why it is used and also an example which helps understanding the DATEFORMAT command in better way.
 
 
Database
I have made use of the following table Medicines with the schema as follows.
DATEFORMAT command, uses and example in SQL Server
 
Note: You can download the database table SQL by clicking the download link below.
         Download SQL file
 
 

DATEFORMAT command
The default Date format in a SQL Server DateTime column is yyyy-MM-dd. SQL Server provided a command named DATEFORMAT which allows us to insert Date in the desired format such as dd/MM/yyyy.
The DATEFORMAT command has different date format options such as
1. DMY – dd/MM/yyyy. Ex: 13/06/2018.
2. YDM – yyyy/dd/MM. Ex: 2018/13/06.
3. MDY – MM/dd/yyyy. Ex: 06/13/2018.
4. YMD – yyyy/MM/dd. Ex: 2018/06/13.
 
 
Using the DATEFORMAT command in Stored Procedure
Inside the Insert_Medicine Stored Procedure, the Date parameters are of VARCHAR data type so that the Date values can be passed in dd/MM/yyyy format.
Before the INSERT statement, the DATEFORMAT command is executed with DMY option which notifies SQL Server that the values of Dates will be in dd/MM/yyyy format.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Insert_Medicine
      @Name VARCHAR(50),
      @ManufacturingDate VARCHAR(10),
      @ExpiryDate VARCHAR(10)
AS
BEGIN
      SET NOCOUNT ON;
     
      SET DATEFORMAT DMY
     
      INSERT INTO Medicines
      VALUES(@Name, @ManufacturingDate, @ExpiryDate)
END
GO
 
Output
DATEFORMAT command, uses and example in SQL Server