Here, we will learn how to convert SQL DateTime data type values from one format to another like mm/dd/yyyy, yyyy-mm-dd, dd-mm-yy hh-mm-ss, yyyymmdd, etc. using CONVERT and FORMAT functions with examples.
While working with the applications we will get a requirement to change the default datetime data type values to the different formats based on the requirement of the application.
The default datetime data type value will be as shown below.
In many cases, we don’t need to return the complete date and time instead we require only the date/time or both date and time in different formats.
To perform different date and time conversions in SQL server, we have a function called CONVERT by using that we can convert the given datetime values to different date and time formats based on our requirements.
Following is the syntax of the CONVERT function in the SQL server.
The Convert function will accept three parameters, which are
Here is an example of how to convert a datetime value to a different format using the CONVERT function in the SQL server.
In this SQL CONVERT function example, we are using GETDATE() function to get the current date and time, and then convert it to varchar data type using the format code 120, which represents the format “yyyy-mm-dd hh:mi:ss”.
In SQL, the GETDATE() function will return the current date and time of the server where the SQL Server is running. In case, if you need a universal (UTC) date and time, you need to use GETUTCDATE().
The GETUTCDATE() function will return the current date and time in Greenwich Mean Time (GMT) and it is useful when you want to store/compare dates and times across different time zones.
Following is the example of using both the GETDATE() and GETUTCDATE() functions in SQL.
The above SQL query will return the result as shown below.
| CurrentDateTime | CurrentUTCDateTime |
|---|---|
| 2023-03-21 22:12:24.437 | 2023-03-21 16:42:24.437 |
Now we will learn some of the commonly used date and time conversions in sql server with examples.
SQL Convert DateTime to yyyymmdd Format
By using format code 112, we can convert the given datetime to yyyymmdd format using the CONVERT function in sql server.
SQL Convert DateTime to dd/mm/yyyy Format
By using format code 103, we can convert the given datetime to dd/mm/yyyy format.
SQL DateTime in dd-mm-yyyy hh:mm:ss:nnn Format
By using format code 29, we can convert the given datetime to dd-mm-yyyy hh:mm:ss:nnn format.
SQL DateTime in yyyy-mm-dd Format
By using format code 31, we can convert the given datetime to yyyy-mm-dd format.
SQL DateTime in mm/dd/yyyy Format
By using format code 101, we can convert the given datetime to mm/dd/yyyy format.
SQL DateTime in yyyymmdd Format
By using format code 112, we can convert the given datetime to yyyymmdd format.
SQL Datetime in Mon dd yyyy hh:mm AM/PM Format
By using format code 0, we can convert the given datetime to mon dd yyyy hh:mm AM/PM format.
SQL DateTime in hh:mm:ss Format
By using format code 108, we can convert the given datetime to hh:mm:ss format.
Like this, we have different format codes available to format the datetime data type value to the required format. The following table lists all the available format codes in SQL Server to convert or format the given datetime value based on our requirements.
| Format | Query | Example |
|---|---|---|
| mon dd yyyy hh:mm AM/PM | SELECT CONVERT(VARCHAR, GETDATE(), 0) | Mar 22 2023 10:46AM |
| mm/dd/yy | SELECT CONVERT(VARCHAR, GETDATE(), 1) | 03/22/23 |
| yy.mm.dd | SELECT CONVERT(VARCHAR, GETDATE(), 2) | 23.03.22 |
| dd/mm/yy | SELECT CONVERT(VARCHAR, GETDATE(), 3) | 22/03/23 |
| dd.mm.yy | SELECT CONVERT(VARCHAR, GETDATE(), 4) | 22.03.23 |
| dd-mm-yy | SELECT CONVERT(VARCHAR, GETDATE(), 5) | 22-03-23 |
| dd-mon-yy | SELECT CONVERT(VARCHAR, GETDATE(), 6) | 22 Mar 23 |
| mon dd, yy | SELECT CONVERT(VARCHAR, GETDATE(), 7) | Mar 22, 23 |
| hh:mm:ss | SELECT CONVERT(VARCHAR, GETDATE(), 8) | 10:46:21 |
| mon dd yyyy hh:mm:ss:nnn AM/PM | SELECT CONVERT(VARCHAR, GETDATE(), 9) | Mar 22 2023 10:46:21:633AM |
| mm-dd-yy | SELECT CONVERT(VARCHAR, GETDATE(), 10) | 03-22-23 |
| yy/mm/dd | SELECT CONVERT(VARCHAR, GETDATE(), 11) | 23/03/22 |
| yymmdd | SELECT CONVERT(VARCHAR, GETDATE(), 12) | 230322 |
| dd mon yyyy hh:mm:ss:nnn AM/PM | SELECT CONVERT(VARCHAR, GETDATE(), 13) | 22 Mar 2023 10:46:21:633 |
| hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 14) | 10:46:21:633 |
| yyyy-mm-dd hh:mm:ss | SELECT CONVERT(VARCHAR, GETDATE(), 20) | 2023-03-22 10:46:21 |
| yyyy-mm-dd hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 21) | 2023-03-22 10:46:21.633 |
| mm/dd/yy hh:mm:ss AM/PM | SELECT CONVERT(VARCHAR, GETDATE(), 22) | 03/22/23 10:46:21 AM |
| yyyy-mm-dd | SELECT CONVERT(VARCHAR, GETDATE(), 23) | 2023-03-22 |
| hh:mm:ss | SELECT CONVERT(VARCHAR, GETDATE(), 24) | 10:46:21 |
| yyyy-mm-dd hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 25) | 2023-03-22 10:46:21.633 |
| yyyy-dd-mm hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 26) | 2023-22-03 10:46:21.633 |
| mm-dd-yyyy hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 27) | 03-22-2023 10:46:21.633 |
| mm-yyyy-dd hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 28) | 03-2023-22 10:46:21.633 |
| dd-mm-yyyy hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 29) | 22-03-2023 10:46:21.633 |
| dd-yyyy-mm hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 30) | 22-2023-03 10:46:21.633 |
| yyyy-dd-mm | SELECT CONVERT(VARCHAR, GETDATE(), 31) | 2023-22-03 |
| mm-dd-yyyy | SELECT CONVERT(VARCHAR, GETDATE(), 32) | 03-22-2023 |
| mm-yyyy-dd | SELECT CONVERT(VARCHAR, GETDATE(), 33) | 03-2023-22 |
| dd-mm-yyyy | SELECT CONVERT(VARCHAR, GETDATE(), 34) | 22-03-2023 |
| dd-yyyy-mm | SELECT CONVERT(VARCHAR, GETDATE(), 35) | 22-2023-03 |
| mon dd yyyy hh:mm AM/PM | SELECT CONVERT(VARCHAR, GETDATE(), 100) | Mar 22 2023 10:46AM |
| mm/dd/yyyy | SELECT CONVERT(VARCHAR, GETDATE(), 101) | 03/22/2023 |
| yyyy.mm.dd | SELECT CONVERT(VARCHAR, GETDATE(), 102) | 2023.03.22 |
| dd/mm/yyyy | SELECT CONVERT(VARCHAR, GETDATE(), 103) | 22/03/2023 |
| dd.mm.yyyy | SELECT CONVERT(VARCHAR, GETDATE(), 104) | 22.03.2023 |
| dd-mm-yyyy | SELECT CONVERT(VARCHAR, GETDATE(), 105) | 22-03-2023 |
| dd Mon yyyy | SELECT CONVERT(VARCHAR, GETDATE(), 106) | 22 Mar 2023 |
| mon dd, yyyy | SELECT CONVERT(VARCHAR, GETDATE(), 107) | Mar 22, 2023 |
| hh:mm:ss | SELECT CONVERT(VARCHAR, GETDATE(), 108) | 10:46:21 |
| mon dd yyyy hh:mm:ss:nnn AM/PM | SELECT CONVERT(VARCHAR, GETDATE(), 109) | Mar 22 2023 10:46:21:633AM |
| mm-dd-yyyy | SELECT CONVERT(VARCHAR, GETDATE(), 110) | 03-22-2023 |
| yyyy/mm/dd | SELECT CONVERT(VARCHAR, GETDATE(), 111) | 2023/03/22 |
| yyyymmdd | SELECT CONVERT(VARCHAR, GETDATE(), 112) | 20230322 |
| dd mon yyyy hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 113) | 22 Mar 2023 10:46:21:633 |
| hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 114) | 10:46:21:633 |
| hhmmss | SELECT CONVERT(VARCHAR, GETDATE(), 115) | 104621 |
| yyyy-mm-dd hh:mm:ss | SELECT CONVERT(VARCHAR, GETDATE(), 120) | 2023-03-22 10:46:21 |
| yyyy-mm-dd hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 121) | 2023-03-22 10:46:21.633 |
| yyyy-mm-dd T hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 126) | 2023-03-22T10:46:21.633 |
| yyyy-mm-dd T hh:mm:ss:nnn | SELECT CONVERT(VARCHAR, GETDATE(), 127) | 2023-03-22T10:46:21.633 |
| dd mmm yyyy hh:mi:ss:nnn AM/PM | SELECT CONVERT(VARCHAR, GETDATE(), 130) | 1 رمضان 1444 10:46:21:633AM |
| dd mmm yyyy hh:mi:ss:nnn AM/PM | SELECT CONVERT(VARCHAR, GETDATE(), 131) | 1/09/1444 10:46:21:633AM |
If the required datetime format value is not found in the provided format codes, you can customize the datetime value by using the FORMAT function in sql server.
In SQL, the FORMAT function is also useful to convert the given string/number/datetime value to the specified format.
Following is the syntax of defining the FORMAT function in SQL.
The FORMAT function will accept two parameters, which are
Following is the example of using the FORMAT function to convert the datetime value to dd-mm-yyyy hh:mm format.
SQL DateTime in MMM dd yyyy Format
By using the FORMAT function, we can convert the datetime value to MMM dd yyyy format.
SQL DateTime in hh:mm:ss AM/PM Format
We can convert the datetime value to hh:mm:ss AM/PM using the FORMAT function as shown below.
SQL Format DateTime with Culture
In SQL, we can do the regional formatting using the culture option in the FORMAT function as shown below.
English – INDIA
English - USA
French - FRANCE
Japanese - JAPAN
This is how you can use CONVERT and FORMAT functions in SQL to convert/format the given datetime data type values to the required format.