Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

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

  • Last Updated : 28 Nov, 2021

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 dor datetime 

and smalldatetime

mon dd yyy hh:

 miAM (or PM)

1101U.S.

1 = mm/dd/yy

101 = mm/dd/yyyy

2102ANSI

2 = yy.mm.dd

102 = yyyy.mm.dd

3103British/French

3 = dd/mm/yy

103 = dd/mm/yyyy

4104German

4 = dd.mm.yy

104 = dd.mm.yyyy

11111JAPAN

11 = yy/mm/dd

111 = yyyy/mm/dd

12112ISO

12 = yymmdd

112 = yyyymmdd

13 or 113 (1,2)Europe default + millisecondsdd 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.

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!