Introduction
One of the fundamental tasks in database management is formatting dates correctly. In Structured Query Language (SQL), the server's local language settings determine date and time formats. There may be situations where you need to change the date format. You might need to meet the requirements of your organization or third-party systems.
Formatting dates is important because it ensures that your data is consistent and easy to read. Incorrect date formatting can lead to confusion and errors. This can have serious consequences in a database.
For example, if you are storing dates in a financial database. Improperly formatted dates could lead to incorrect calculations and inaccurate reports.
In this article, we will explore the different methods for changing the date format in SQL. There are over 30 code examples to help you improve your SQL code quality.
Whether you are new to SQL or an experienced database administrator, this guide will provide you with the tools and knowledge you need to manage dates effectively in your database.
Using the CONVERT Function to Format Dates and Times in SQL Server
Here we will use the "CONVERT" function to convert a datetime into a different format in SQL Server.
By using some built-in function in SQL Server, we can get the datetime value in a specific format.
Example
- GETDATE(): It returns server datetime in “YYYY-MM-DD HH:mm:ss.fff” format.
- GETUTCDATE(): It returns datetime in GMT.
- SYSDATETIME(): It returns server’s datetime
- SYSDATETIMEOFFSET(): It returns server’s datetime with time zone on which SQL Server instance is running.
- SYSUTCDATETIME(): It returns server datetime in GMT.
- CURRENT_TIMESTAMP: It returns current datetime of the server.
SELECT GETDATE() AS [GETDATE()],
GETUTCDATE() AS [GETUTCDATE()],
SYSDATETIME() AS [SYSDATETIME()],
SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET()],
SYSUTCDATETIME() AS [SYSUTCDATETIME()],
CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP];
GETDATE():
GETUTCDATE():
SYSDATETIME():
SYSDATETIMEOFFSET():
SYSUTCDATETIME():
CURRENT_TIMESTAMP:
By using the “CONVERT()” function, we can convert the date and time to a different format.
Output
2022-01-02 09:49:53.077
2022-01-02 04:19:53.077
2022-01-02 09:49:53.0783654
2022-01-02 09:49:53.0783654 +05:30
2022-01-02 04:19:53.0783654
2022-01-02 09:49:53.077
Syntax
CONVERT(Datatype, Date, Formatcode);
It accepts three arguments.
- Datatype: It defines the target data type we want to be converted
- Date: It defines the date value we want to convert.
- Fromatcode: It defines the output format of the date value.
SELECT CONVERT(VARCHAR, GETDATE(), 1)
Output
01/02/22
There are various datetime format codes, by using this code we can change the format of a datetime.
SQL Date Convert
MM/DD/YY Format
By using format code as 1 we can get datetime in “MM/DD/YY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 1)
Output
01/02/22
YY.MM.DD Format
By using format code as 2 we can get datetime in “YY.MM.DD” format.
SELECT CONVERT(VARCHAR, GETDATE(), 2)
Output
22.01.02
DD/MM/YY Format
By using format code as 3 we can get datetime in “DD/MM/YY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 3)
Output
02/01/22
DD.MM.YY Format
By using format code as 4 we can get datetime in “DD.MM.YY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 4)
Output
02.01.22
DD-MM-YY Format
By using format code as 5 we can get datetime in “DD-MM-YY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 5)
Output
02-01-22
DD MMM YY Format
By using format code as 6 we can get datetime in “DD MMM YY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 6)
Output
02 Jan 22
MMM DD, YY Format
By using format code as 7 we can get datetime in “MMM DD, YY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 7)
Output
Jan 02, 22
MMM DD YYYY hh:mm:ss:fff(AM/PM) Format
By using format code as 9 we can get datetime in “MMM DD YYYY hh:mm:ss:fff(AM/PM)” format.
SELECT CONVERT(VARCHAR, GETDATE(), 9)
Output
Jan 4 2022 10:28:42:097AM
MM-DD-YY Format
By using format code as 10 we can get datetime in “MM-DD-YY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 10)
Output
01-02-22
YY/MM/DD Format
By using format code as 11 we can get datetime in “YY/MM/DD” format.
SELECT CONVERT(VARCHAR, GETDATE(), 11)
Output
22/02/01
YYMMDD Format
By using format code as 12 we can get datetime in “YYMMDD” format.
SELECT CONVERT(VARCHAR, GETDATE(), 12)
Output
220401
DD MMM YYYY HH:mm:ss:fff Format
By using format code as 13 we can get datetime in “DD MMM YYYY HH:mm:ss:fff” format.
SELECT CONVERT(VARCHAR, GETDATE(), 13)
Output
02 Jan 2022 10:43:52:753
HH:mm:ss:fff Format
By using format code as 14 we can get datetime in “HH:mm:ss:fff” format.
SELECT CONVERT(VARCHAR, GETDATE(), 14)
Output
10:45:16:290
YYYY-MM-DD HH:mm:ss Format
By using format code as 20 we can get datetime in “YYYY-MM-DD HH:mm:ss” format.
SELECT CONVERT(VARCHAR, GETDATE(), 20)
Output
2022-01-02 10:46:58
YYYY-MM-DD HH:mm:ss.fff Format
By using format code as 21 we can get datetime in “YYYY-MM-DD HH:mm:ss.fff” format.
SELECT CONVERT(VARCHAR, GETDATE(), 21)
Output
2022-01-02 10:47:58.393
MM/DD/YY hh:mm:ss (AM/PM) Format
By using format code as 22 we can get datetime in “MM/DD/YY hh:mm:ss (AM/PM)” format.
SELECT CONVERT(VARCHAR, GETDATE(), 22)
Output
01/02/22 10:49:36 AM
YYYY-MM-DD Format
By using format code as 23 we can get datetime in “YYYY-MM-DD” format.
SELECT CONVERT(VARCHAR, GETDATE(), 23)
Output
2022-01-02
HH:mm:ss Format
By using format code as 24 we can get datetime in “HH:mm:ss” format.
SELECT CONVERT(VARCHAR, GETDATE(), 24)
Output
10:51:38
YYYY-MM-DD HH:mm:ss.fff Format
By using format code as 25 we can get datetime in “YYYY-MM-DD HH:mm:ss.fff” format.
SELECT CONVERT(VARCHAR, GETDATE(), 25)
Output
2022-01-02 10:55:30.010
MMM DD YYYY hh: ss (AM/PM) Format
By using format code as 100 we can get datetime in “MMM DD YYYY hh: ss (AM/PM)” format.
SELECT CONVERT(VARCHAR, GETDATE(), 100)
Output
Jan 4 2022 10:58AM
MM/DD/YYYY Format
By using format code as 101 we can get datetime in “MM/DD/YYYY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 101)
Output
01/02/2022
YYYY.MM.DD Format
By using format code as 102 we can get datetime in “YYYY.MM.DD” format.
SELECT CONVERT(VARCHAR, GETDATE(), 102)
Output
2022.01.02
DD/MM/YYYY Format
By using format code as 103 we can get datetime in “DD/MM/YYYY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 103)
Output
02/01/2022
DD.MM.YY Format
By using format code as 104 we can get datetime in “DD.MM.YY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 104)
Output
02.01.2022
DD-MM-YY Format
By using format code as 105 we can get datetime in “DD-MM-YY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 105)
Output
02-01-2022
DD MMM YYYY Format
By using format code as 106 we can get datetime in “DD MMM YYYY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 106)
Output
02 Jan 2022
MMM DD, YYYY Format
By using format code as 107 we can get datetime in “MMM DD, YYYY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 107)
Output
Jan 02, 2022
HH:mm: ss Format
By using format code as 108 we can get datetime in “HH:mm: ss” format.
SELECT CONVERT(VARCHAR, GETDATE(), 108)
Output
11:06:17
MMM DD YYYY hh:mm:ss:fff(AM/PM) Format
By using format code as 109 we can get datetime in “MMM DD YYYY hh:mm:ss:fff(AM/PM)” format.
SELECT CONVERT(VARCHAR, GETDATE(), 109)
Output
Jan 4 2022 11:07:17:280AM
MM- DD-YY Format
By using format code as 110 we can get datetime in “MM- DD-YY” format.
SELECT CONVERT(VARCHAR, GETDATE(), 110)
Output
01-02-2022
YYYY/MM/DD Format
By using format code as 111 we can get datetime in “YYYY/MM/DD” format.
SELECT CONVERT(VARCHAR, GETDATE(), 111)
Output
2022/01/02
YYYYMMDD Format
By using format code as 112 we can get datetime in “YYYYMMDD” format.
SELECT CONVERT(VARCHAR, GETDATE(), 112)
Output
20220104
DD MMM YYYY HH:mm:ss: fff Format
By using format code as 113 we can get datetime in “DD MMM YYYY HH:mm:ss: fff” format.
SELECT CONVERT(VARCHAR, GETDATE(), 113)
Output
02 Jan 2022 11:11:30:503
HH:mm:ss: fff Format
By using format code as 114 we can get datetime in “HH:mm:ss: fff” format.
SELECT CONVERT(VARCHAR, GETDATE(), 114)
Output
11:12:13:973
YYYY-MM-DD HH:mm: ss Format
By using format code as 120 we can get datetime in “YYYY-MM-DD HH:mm: ss” format.
SELECT CONVERT(VARCHAR, GETDATE(), 120)
Output
2022-01-02 11:13:13
YYYY-MM-DD HH:mm: ss.fff Format
By using format code as 121 we can get datetime in “YYYY-MM-DD HH:mm: ss.fff” format.
SELECT CONVERT(VARCHAR, GETDATE(), 121)
Output
2022-01-02 11:14:10.470
YYYY-MM-DDTHH:mm: ss.fff Format
By using format code as 126/127 we can get datetime in “YYYY-MM-DDTHH:mm: ss.fff” format.
SELECT CONVERT(VARCHAR, GETDATE(), 126)
SELECT CONVERT(VARCHAR, GETDATE(), 127)
Output
2022-01-04T11:15:03.223
YYYY-MM-DDTHH:mm: ss.fff Format
By using format code as 130/131 we can get datetime in “Islamic/Hijri” date format.
SELECT CONVERT(VARCHAR, GETDATE(), 131)
Output
1/06/1443 11:24:36:480AM
In the above article, we learned how to convert DateTime to different formats in SQL Server.
Hope this will help the readers. Happy Coding!!!