In this article I will explain with an example, how to insert and select Date in dd/MM/yyyy format in DateTime Column of SQL Server database.
This article will illustrate how to use Stored Procedures for inserting and selecting Dates in dd/MM/yyyy format.
 
 
Database
I have made use of the following table Medicines with the schema as follows.
Insert and Select Date in dd/MM/yyyy format in SQL Server
 
Note: You can download the database table SQL by clicking the download link below.
         Download SQL file
 
 
Concept
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.
 
 
Insert Date in dd/MM/yyyy format using Stored Procedure in SQL Server
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
Insert and Select Date in dd/MM/yyyy format in SQL Server
 
 
Select Date in dd/MM/yyyy format using Stored Procedure in SQL Server
Inside the Select_Medicine Stored Procedure, the Date column values are converted into dd/MM/yyyy format using the CONVERT function and passing value 103 for the third parameter.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE Select_Medicine
      @MedicineId INT
AS
BEGIN
      SET NOCOUNT ON;
           
      SELECT [Name]
            ,CONVERT(VARCHAR(10), [ManufacturingDate], 103) [ManufacturingDate]
            ,CONVERT(VARCHAR(10), [ExpiryDate], 103) [ExpiryDate]
      FROM Medicines
      WHERE MedicineId = @MedicineId
END
GO
 
Output
Insert and Select Date in dd/MM/yyyy format in SQL Server