How to Specify a Date Format on Creating a Table and Inserting Values in SQL?
Last Updated :
18 Dec, 2024
Dates are a fundamental part of database management, as they provide a timeline for analyzing data trends, tracking records, and scheduling events. In SQL, handling and formatting date columns correctly ensures consistency and clarity in our database. One critical aspect of working with dates is specifying their format while creating a table and inserting values.
Data Types for Handling Dates in SQL
SQL provides several data types for handling dates and times:
- DATE: Stores date values (e.g., year, month, day).
- DATETIME: Stores both date and time values (e.g., year, month, day, hour, minute, second).
- TIMESTAMP: Similar to
DATETIME
but with additional precision (e.g., fractional seconds or time zones).
1. Creating a Table with a Date Fields
To define a date column in a table, use the appropriate data type (e.g., DATE
, DATETIME
) during table creation. Below are examples:
Example 1: Table with a Simple Date Field
To demonstrate, let’s create a table called Employees that stores user information, including EmployeeID, Name and HireDate. Here, we also have created a column named Dt_FORMATTED where we are going to save our formatted Date
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
HireDate DATE
);
Example 2: Table with a Formatted Date Column Using CONVERT()
Now, we see the CONVERT() function. The CONVERT() function simply converts a value of any type into a specified datatype. 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.
Syntax
CONVERT ( data_type ( length ) ,
expression , style )
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)
);
Explanation:
CONVERT()
converts the Dt
column into a German date format (dd.mm.yyyy
) and saves it in Dt_FORMATTED
.
- The 104 style code specifies the German format.
2. Inserting Dates into the Table with Specific Formats
When inserting dates into a table, it’s important to specify the correct format. SQL uses the SET DATEFORMAT
command to interpret date strings correctly
Query:
SET DATEFORMAT dmy; INSERT INTO GFG_user
(Id, Dt, Address) VALUES ('1','23.11.2021',
'German');
Explanation:
- The
SET DATEFORMAT dmy
command ensures the date string is interpreted as day.month.year
.
- Without this, SQL Server might assume a default
month.day.year
format, leading to errors.
Common Error Avoidance
- For example, suppose we 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 out .
- Not mentioning the DATEFORMAT might give us 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 us to get rid of it.
Output
Id |
Dt |
Address |
Dt_FORMATTED |
1 |
2021-11-23 |
German |
23.11.2021 |
3. Using TO_DATE()
for Custom Formats (Oracle)
For databases like Oracle, TO_DATE()
function is commonly used to specify and insert dates in a particular format. TO_DATE()
function converts strings into dates using a specified format. We can also insert date using ‘to_date‘ function in sql. The following syntax can be used:
Syntax:
TO_DATE([value], [format]);
Example
Let’s create another table called GFG_Intern
to demonstrate the use of TO_DATE()
. Format of date can be of different types such as: ‘dd-mm-yyyy’, ‘yyyy-mm-dd’, ‘mm-dd-yyyy’.
CREATE TABLE GFG_Intern (
e_name VARCHAR(20),
e_id NUMBER,
joiningDate DATE
);
INSERT INTO GFG_Intern VALUES ('FirstIntern', 1, TO_DATE('2022-12-12', 'yyyy-mm-dd'));
INSERT INTO GFG_Intern VALUES ('SecondIntern', 2, TO_DATE('2022-Dec-12', 'yyyy-mm-dd'));
INSERT INTO GFG_Intern VALUES ('ThirdIntern', 3, TO_DATE('30-12-2022', 'dd-mm-yyyy'));
SELECT * FROM GFG_INTERN;
Output
.png)
Output
Explanation:
TO_DATE('2022-12-12', 'YYYY-MM-DD')
: Converts the string into a date object using the specified format.
- Multiple date formats like
DD-Mon-YYYY
, DD-MM-YYYY
, and MM-DD-YYYY
can be used based on your requirements.
4. Viewing and Formatting Date Queries
We can format or manipulate date columns in queries using functions like CONVERT()
, TO_DATE()
, YEAR()
, and more.
SQL Server Example:
SELECT Id, Dt, FORMAT(Dt, 'dd MMM yyyy') AS FormattedDate FROM GFG_User;
Oracle Example:
SELECT e_name, TO_CHAR(joiningDate, 'DD-Mon-YYYY') AS FormattedDate FROM GFG_Intern;
Output
Id |
Dt |
FormattedDate |
1 |
2024-12-31 |
31 Dec 2024 |
Common Date Format Codes in SQL
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 |
Conclusion
Specifying a date format while creating and populating a table in SQL is essential for maintaining data consistency and avoiding errors. By using commands like SET DATEFORMAT
, functions like CONVERT()
and TO_DATE()
, and best practices, we can manage dates effectively in our database. Implementing these techniques will not only streamline our operations but also ensure our database remains accurate and easy to understand
Similar Reads
How to Specify a Date Format on Creating a Table and Fill it in SQL?
Dates are a fundamental part of database management, as they provide a timeline for analyzing data trends, tracking records, and scheduling events. In SQL, handling and formatting date columns correctly ensures consistency and clarity in our database. One critical aspect of working with dates is spe
5 min read
How to Write a SQL Query For a Specific Date Range and Date Time?
Managing date and time in SQL is a critical task for many real-world applications, especially when handling time-sensitive data such as logs, timestamps, and event records. SQL provides various tools and data types to work with date and time effectively. In this guide, we will focus on working with
4 min read
How to Insert Double and Float Values in SQLite?
SQLite, a lightweight and server-less relational database management system, provides powerful features for handling various data types, including double and float values. In this article, we will explore various methods along with examples for inserting double and float values into SQLite tables, e
3 min read
How to Add a Column with a Default Value to an Existing Table in SQL Server
In SQL Server sometimes it may be required to set the default value to a Column in a table. This default value will be set as the column value when there is no value inserted or left empty for that particular column while data is inserted into the Table. Sometimes after creating a table, it may be r
8 min read
How to Create One Table From Another Table in SQL
Creating a table based on the structure and data of an existing table is a common task in database management. This process allows us to replicate a table for backup, testing or data transformation purposes. SQL provides efficient methods to create one table from another while preserving the schema,
3 min read
How to Insert a Value that Contains an Apostrophe in SQL?
SQL is a standard database language used to access and manipulate data in databases. SQL stands for Structured Query Language. SQL was developed by IBM Computer Scientists in the 1970s. By executing queries SQL can create, update, delete, and retrieve data in databases like MySQL, Oracle, PostgreSQL
3 min read
PHP date() format when inserting into datetime in MySQL
This problem describes the date format for inserting the date into MySQL database. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The date can be stored in this format only. However, it can be used with any time format functions to change it and display it. When writin
3 min read
How to Query for all Dates Greater Than a Certain Date in SQLite?
SQLite has become the preferred option for embedded databases mainly because of its lightweight attributes and user-friendliness. SQLite with dates greater than that particular date can be achieved by following the datetime functions that are given by the database engine. This possibility is extreme
4 min read
How to get current formatted date dd/mm/yyyy in Javascript and append it to an input?
The current date in "dd/mm/yyyy" format represents the day, month, and year according to the Gregorian calendar, to obtain the current date in "dd/mm/yyyy" format using JavaScript, utilize the Date object methods to extract day, month, and year components, format them accordingly, and then append th
4 min read
How to use moment.js to change date format in jQuery ?
Using Moment.js to change date format in jQuery involves importing the Moment.js library and utilizing its functions to parse, manipulate, and format dates. You can easily convert dates into various formats by calling moment(date).format(desiredFormat) to display the date in your preferred format. m
2 min read