How to Specify a Date Format on Creating a Table and Fill it in SQL?
Whenever we work with databases, we find that almost every single table contains a Date column. After all, the date of the data plays an important role while analyzing it. Storing dates in a specific or understandable format is very important. In this article, we are going to learn how we can specify a Date format on SQL Server.
Let’s create our demo database and table.
Step 1: Create a database
Use the following command to create a database.
CREATE DATABASE User_details;
Step 2:Use database
Step 3: Table definition
We have the following GFG_user table in the database.
CREATE TABLE GFG_user(Id INT NOT NULL,Dt DATE, Address VARCHAR(100),Dt_FORMATTED AS (convert(varchar(255), dt, 104)), PRIMARY KEY (Id) );
Here, we have created a column named Dt_FORMATTED where we are going to save our formatted Date.
Now, we see the CONVERT() function. The CONVERT() function simply converts a value of any type into a specified datatype.
CONVERT ( data_type ( length ) , expression , style )
By using this function, we are casting the string to a date. In the place of style argument, we have mentioned ‘104’. It is a numeric code to specify the date format.
Check this table to see different codes used for different formats: Default dor datetime and smalldatetime mon dd yyy hh: miAM (or PM) 1 = mm/dd/yy 101 = mm/dd/yyyy 2 = yy.mm.dd 102 = yyyy.mm.dd 3 = dd/mm/yy 103 = dd/mm/yyyy 4 = dd.mm.yy 104 = dd.mm.yyyy 11 = yy/mm/dd 111 = yyyy/mm/dd 12 = yymmdd 112 = yyyymmdd
– 0 or 100 (1,2) 1 101 U.S. 2 102 ANSI 3 103 British/French 4 104 German 11 111 JAPAN 12 112 ISO – 13 or 113 (1,2) Europe default + milliseconds dd mon yyyy hh:mi:ss:mmm (24h) – 131 (2) Hijri (5) dd/mm/yyyy hh:mi:ss:mmmAM
Default dor datetime
mon dd yyy hh:
miAM (or PM)
1 = mm/dd/yy
101 = mm/dd/yyyy
2 = yy.mm.dd
102 = yyyy.mm.dd
3 = dd/mm/yy
103 = dd/mm/yyyy
4 = dd.mm.yy
104 = dd.mm.yyyy
11 = yy/mm/dd
111 = yyyy/mm/dd
12 = yymmdd
112 = yyyymmdd
Here, We have mentioned only the 10 most used formats.
Step 4: Insert values
The following command is used to insert values into the table.
SET DATEFORMAT dmy; INSERT INTO GFG_user (Id, Dt, Address) VALUES ('1','23.11.2021', 'German');
In this query, we are using the DATEFORMAT setting.
SET DATEFORMAT format
When we are inserting the string, the server will try to convert the string to date before inserting it into the table. As it cannot tell if we are putting the month before the date or the date before the month. For example, suppose you are trying to insert 06.07.2000. The server is unable to detect if the date is the 6th of July or it is the 7th of June. Though it uses the localization settings of the user account that is operating to figure that out not mentioning the DATEFORMAT might give you an error as most of the times the account that is running the operation is set to USA format, that is – Month Day Year (mdy).
The error was caused because we wanted to save it as dmy, not mdy. However, using DATEFORMAT will help you to get rid of it.
We are done with our table, now let’s check if we are getting our desired output or not.
Step 5: View data of the table
SELECT * FROM GFG_user;
We have successfully got our German format Date in the Dt_FORMATTED column.