Change Date Format Sql Server Database
In this article, we will explore various SQL Convert Date formats to use in writing SQL queries.
We need to work with date type data in SQL. It can be a complicated thing to deal with, at times, for SQL Server developers. Suppose you have a Product table with a column timestamp. It creates a timestamp for each customer order. You might face the following issues with it
- You fail to insert data in the Product table because the application tries to insert data in a different date format
- Suppose you have data in a table in the format YYYY-MM-DD hh:mm: ss. You have a daily Sales report, and in that, you want data group by date. You want to have data in the report in format YYYY-MM-DD
We do face many such scenarios when we do not have a date format as per our requirement. We cannot change table properties to satisfy each requirement. In this case, we need to use the built-in functions in SQL Server to give the required date format.
Data Types for Date and Time
We have the following SQL convert date and Time data types in SQL Server.
Date type | Format |
Time | hh:mm:ss[.nnnnnnn] |
Date | YYYY-MM-DD |
SmallDateTime | YYYY-MM-DD hh:mm:ss |
DateTime | YYYY-MM-DD hh:mm:ss[.nnn] |
DateTime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] |
DateTimeOffset | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm |
In SQL Server, we have used built-in functions such as SQL GETDATE() and GetUTCDate() to provide server date and format in various formats.
- SYSDATETIME(): To returns the server's date and time
- SYSDATETIMEOffset(): It returns the server's date and time, along with UTC offset
- GETUTCDATE(): It returns date and GMT (Greenwich Mean Time ) time
- GETDATE(): It returns server date and time
Execute the following queries to get output in respective formats.
-
Select SYSDATETIME ( ) as [ SYSDATETIME ]
-
Select SYSDATETIMEOffset ( ) as [ SYSDATETIMEOffset ]
-
Select GETUTCDATE ( ) as [ GETUTCDATE ]
-
Select GETDATE ( ) as [ GETDATE ]
SQL Convert Date Formats
As highlighted earlier, we might need to format a date in different formats as per our requirements. We can use the SQL CONVERT() function in SQL Server to format DateTime in various formats.
Syntax for the SQ: CONVERT() function is as follows.
SELECT CONVERT ( data_type ( length ) ) , Date , DateFormatCode ) |
- Data_Type: We need to define data type along with length. In the date function, we use Varchar(length) data types
- Date: We need to specify the date that we want to convert
- DateFormatCode: We need to specify DateFormatCode to convert a date in an appropriate form. We will explore more on this in the upcoming section
Let us explore various date formats using SQL convert date functions.
First, we declare a variable to hold current DateTime using the SQL GETDATE() function with the following query.
declare @ Existingdate datetime Set @ Existingdate = GETDATE ( ) Print @ Existingdate |
We can see various date formats in the following table. You can keep this table handy for reference purpose in the format of Date Time columns.
| | | ||
Datetime format as Standard: U.S.A. |
| | ||
Datetime format in Standard: ANSI |
| | ||
Datetime format in Standard: British/French |
| | ||
Standard: German |
| | ||
Datetime format in Standard: Italian |
| | ||
Datetime format in Standard: Shortened month name |
| | ||
Datetime format in Standard: Shortened month name |
| | ||
Datetime Format Standard: 24 hour time |
| | ||
Datetime format as Standard: Default + milliseconds |
| | ||
Datetime format in Standard: USA |
| | ||
Datetime format in Standard: JAPAN |
| | ||
Datetime format in Standard: ISO |
| | ||
Datetime format in Standard: Europe default + milliseconds |
| | ||
Datetime format in Standard: 24 hour time with milliseconds |
| | ||
Datetime format in Default: ODBC canonical |
| | ||
Datetime format in Standard: ODBC canonical with milliseconds |
| | ||
Datetime format in Standard: USA with Time AM/PM |
| | ||
|
| | ||
|
| | ||
|
| | ||
Datetime format in Standard: Default |
| | ||
Datetime format in Standard: USA |
| | ||
Datetime format in Standard: ANSI |
| | ||
Datetime format in Standard: British/French |
| | ||
Datetime format in Standard: German |
| | ||
Datetime format in Standard: Italian |
| | ||
Datetime format in Standard: Shortened month name |
| | ||
Datetime format in Standard: Shortened month name |
| | ||
Datetime Format Standard: 24 hour time |
| | ||
Standard: Default + milliseconds |
| | ||
Datetime format in Standard: USA |
| | ||
Datetime format in Standard: JAPAN |
| | ||
Datetime format in Standard: ISO |
| | ||
Datetime format in Standard: Europe default + milliseconds |
| | ||
Datetime format in Standard: 24 hour time with milliseconds |
| | ||
Datetime format in Default: ODBC canonical |
| | ||
Datetime format in Standard: ODBC canonical with milliseconds |
| | ||
Datetime format in Standard: ISO8601 |
| | ||
Datetime format in Standard: Islamic/Hijri date |
| |
In the table, we can see various formats to SQL convert date as per your requirements. In the following table, you can see all SQL date formats together.
Date format option | SQL convert date output |
0 | Dec 30 2006 12:38AM |
1 | 12/30/06 |
2 | 06.12.30 |
3 | 30/12/2006 |
4 | 30.12.06 |
5 | 30/12/2006 |
6 | 30-Dec-06 |
7 | Dec 30, 06 |
8 | 00:38:54 |
9 | Dec 30 2006 12:38:54:840AM |
10 | 12-30-06 |
11 | 06/12/1930 |
12 | 61230 |
13 | 30 Dec 2006 00:38:54:840 |
14 | 00:38:54:840 |
20 | 30/12/2006 00:38 |
21 | 38:54.8 |
22 | 12/30/06 12:38:54 AM |
23 | 30/12/2006 |
24 | 00:38:54 |
25 | 38:54.8 |
26 | 2006-30-12 00:38:54.840 |
27 | 12-30-2006 00:38:54.840 |
28 | 12-2006-30 00:38:54.840 |
29 | 38:54.8 |
30 | 30-2006-12 00:38:54.840 |
31 | 2006-30-12 |
32 | 12-30-2006 |
33 | 12-2006-30 |
34 | 30/12/2006 |
35 | 30-2006-12 |
100 | Dec 30 2006 12:38AM |
101 | 12/30/2006 |
102 | 2006.12.30 |
103 | 30/12/2006 |
104 | 30.12.2006 |
105 | 30/12/2006 |
106 | 30-Dec-06 |
107 | Dec 30, 2006 |
108 | 00:38:54 |
109 | Dec 30 2006 12:38:54:840AM |
110 | 12-30-2006 |
111 | 30/12/2006 |
112 | 20061230 |
113 | 30 Dec 2006 00:38:54:840 |
114 | 00:38:54:840 |
120 | 30/12/2006 00:38 |
121 | 38:54.8 |
126 | 2006-12-30T00:38:54.840 |
127 | 2006-12-30T00:38:54.840 |
130 | 10 ?? ????? 1427 12:38:54:840A |
131 | 10/12/1427 12:38:54:840AM |
Let us next explore a function that is useful for SQL convert date.
DATEADD
We can use the SQL DATEADD function to add a particular period to our date. Suppose we have a requirement to add 1 month to current date. We can use the SQL DATEADD function to do this task.
The syntax for SQL DATEADD function is as following
DATEADD ( interval , number , date ) |
Interval: We can specify an interval that needs to be added in the specified date. We can have values such as year, quarter, month, day, week, hour, minute etc.
Number: It specifies the number of the interval to add. For example, if we have specified interval as Month and Number as 2, it means 2 months needs to be added in date.
In the following query, we want to add 2 months in the current date.
SELECT GETDATE ( ) as Currentdate SELECT DATEADD ( month , 2 , GETDATE ( ) ) AS NewDate ; |
You can see the output in the following screenshot.
Similarly, lets us add 1 year to current date using the following query.
select GETDATE ( ) as Currentdate SELECT DATEADD ( Year , 1 , GETDATE ( ) ) AS NewDate ; |
We can combine the SQL DATEADD and CONVERT functions to get output in desired DateTime formats. Suppose, in the previous example; we want a date format in of MMM DD, YYYY. We can use the format code 107 to get output in this format.
Execute the following code to get New date and ConvertedDate.
SELECT DATEADD ( YEAR , 1 , GETDATE ( ) ) AS [ NewDate ] , CONVERT ( varchar ( 110 ) , DATEADD ( YEAR , 1 , GETDATE ( ) ) , 107 ) AS [ ConvertedDate ] |
Conclusion
In this article, we explored various SQL convert date formats. It allows getting a date in required format with Covert function easily. You can use this article to take a reference for all date formats and use in your queries.
- Author
- Recent Posts
As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date.
He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious "Best author of the year" continuously in 2020 and 2021 at SQLShack.
Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
Change Date Format Sql Server Database
Source: https://www.sqlshack.com/sql-convert-date-functions-and-formats/