We can use FORMAT() function to format DATE/TIME data value otherwise we can use CONVERT() function.
Working with date and time we often need the right format so the system doesn't read it as an error format. Because the form of writing the date/time really depends on the regional setting on the machine being used
The safest date format to use is YYYY-MM-dd or if included with time it becomes YYYY-MM-DD hh:mm:ss. This format can be read in various regional date settings.
The following data types in SQL Server related to date and time:
To change the date format in SQL Server we use the CONVERT function. The syntax used is as follows:
SELECT CONVERT (data_type(length)),Date, DateFormatCode)
- Data_Type: complete data type with its size, we usually use the VARCHAR(length) data type as the result of the date format to be created.
- Date: Date value to be formatted
- DateFormatCode: The sequence code is a number that represents a specific date format.
Contoh:
SELECT CONVERT(VARCHAR, GETDATE() ,0) AS tanggal
List of DateFormatCode with how its result:
| Date format option | SQL convert date output |
| 0 | Dec 30 2021 12:38AM |
| 1 | 12/30/2021 |
| 2 | 06.12.30 |
| 3 | 30/12/2021 |
| 4 | 30.12.06 |
| 5 | 30/12/2021 |
| 6 | 30-Dec-06 |
| 7 | 30-Dec-06 |
| 8 | 0:38:54 |
| 9 | Dec 30 2021 12:38:54:840AM |
| 10 | 12/30/2021 |
| 11 | 6/12/1930 |
| 12 | 61230 |
| 13 | 30 Dec 2021 00:38:54:840 |
| 14 | 00:38:54:840 |
| 20 | 30/12/2021 00:38 |
| 21 | 38:54.8 |
| 22 | 12/30/2021 0:38 |
| 23 | 30/12/2021 |
| 24 | 0:38:54 |
| 25 | 38:54.8 |
| 26 | 2021-30-12 00:38:54.840 |
| 27 | 38:54.8 |
| 28 | 12-2021-30 00:38:54.840 |
| 29 | 38:54.8 |
| 30 | 30-2021-12 00:38:54.840 |
| 31 | 2021-30-12 |
| 32 | 12/30/2021 |
| 33 | 12-2021-30 |
| 34 | 30/12/2021 |
| 35 | 30-2021-12 |
| 100 | Dec 30 2021 12:38AM |
| 101 | 12/30/2021 |
| 102 | 2021.12.30 |
| 103 | 30/12/2021 |
| 104 | 30.12.2021 |
| 105 | 30/12/2021 |
| 106 | 30-Dec-06 |
| 107 | 30-Dec-06 |
| 108 | 0:38:54 |
| 109 | Dec 30 2021 12:38:54:840AM |
| 110 | 12/30/2021 |
| 111 | 30/12/2021 |
| 112 | 20211230 |
| 113 | 30 Dec 2021 00:38:54:840 |
| 114 | 00:38:54:840 |
| 120 | 30/12/2021 00:38 |
| 121 | 38:54.8 |
| 126 | 2021-12-30T00:38:54.840 |
| 127 | 2021-12-30T00:38:54.840 |
| 130 | 7 رمضان 1443 8:17:54:640AM -> should be NVARCHAR |
| 131 | 26/05/1443 2:38:54:840AM |
0 Comments