Open In App

How to Specify a Date Format on Creating a Table and Fill it in SQL?

Improve
Improve
Like Article
Like
Save
Share
Report

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.

Query:

CREATE DATABASE User_details; 

Step 2:Use database

Query:

USE User_details; 

Step 3: Table definition

We have the following GFG_user table in the database.

Query: 

CREATE TABLE GFG_user(Id INT NOT NULL,Dt DATE, 
Address  VARCHAR(100),Dt_FORMATTED AS 
(convert(varchar(255), dt, 104)),   
PRIMARY KEY (Id) );

Output:

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.

Syntax:

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:

With century

 (yy) 

With century 

(yyyy)

Standard

Input/Output 

0 or 100 (1,2)

Default for datetime 

and smalldatetime

mon dd yyy hh:

 miAM (or PM)

1 101 U.S.

1 = mm/dd/yy

101 = mm/dd/yyyy

2 102 ANSI

2 = yy.mm.dd

102 = yyyy.mm.dd

3 103 British/French

3 = dd/mm/yy

103 = dd/mm/yyyy

4 104 German

4 = dd.mm.yy

104 = dd.mm.yyyy

11 111 JAPAN

11 = yy/mm/dd

111 = yyyy/mm/dd

12 112 ISO

12 = yymmdd

112 = yyyymmdd

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

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.

Query:

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.

Syntax:

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.

Output:

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

Query:

SELECT * FROM GFG_user; 

Output:

We have successfully got our German format Date in the Dt_FORMATTED column.

Another approach to insert date in database:

We can also insert date using ‘to_date’ function in sql. The following syntax can be used:

TO_DATE([value], [format]);

Format of date can be of different types such as: ‘dd-mm-yyyy’, ‘yyyy-mm-dd’, ‘mm-dd-yyyy’.

Example:

CREATE TABLE GFG_INTERN (e_name VARCHAR(20), e_id number, joiningDate date);

INSERT INTO GFG_INTERN VALUES ('FirstIntern', 001, to_date('2022-12-12','yyyy-mm-dd'));
INSERT INTO GFG_INTERN VALUES ('SecondIntern', 002, to_date('2022-Dec-12','yyyy-mm-dd'));
INSERT INTO GFG_INTERN VALUES ('ThirdIntern', 003, to_date('30-12-2022','dd-mm-yyyy'));
INSERT INTO GFG_INTERN VALUES ('FourthIntern', 004, to_date('12-31-2022','mm-dd-yyyy'));

SELECT * FROM GFG_INTERN;

sql commands

Output:

Output


Last Updated : 03 Feb, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads