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.
I have made use of the following table Medicines with the schema as follows.
Note: You can download the database table SQL by clicking the download link below.
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
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE Insert_Medicine
SET NOCOUNT ON;
SET DATEFORMAT DMY
INSERT INTO Medicines
VALUES(@Name, @ManufacturingDate, @ExpiryDate)