How To Create Custom format in date time ?
SQL Server
provides a number of options you can use to format a date/time string.
One of the first considerations is the actual date/time needed. The most
common is the current date/time using getdate(). This provides
the current date and time according to the server providing the date and
time. If a universal date/time is needed, then getutcdate()should
be used. To change the format of the date, you convert the requested
date to a string and specify the format number corresponding to the
format needed. Below is a list of formats and an example of the output:
| DATE FORMATS | ||
| Format # | Query (current date: 12/30/2006) | Sample |
| 1 | select convert(varchar, getdate(), 1) | 12/30/06 |
| 2 | select convert(varchar, getdate(), 2) | 06.12.30 |
| 3 | select convert(varchar, getdate(), 3) | 30/12/06 |
| 4 | select convert(varchar, getdate(), 4) | 30.12.06 |
| 5 | select convert(varchar, getdate(), 5) | 30-12-06 |
| 6 | select convert(varchar, getdate(), 6) | 30 Dec 06 |
| 7 | select convert(varchar, getdate(), 7) | Dec 30, 06 |
| 10 | select convert(varchar, getdate(), 10) | 12-30-06 |
| 11 | select convert(varchar, getdate(), 11) | 06/12/30 |
| 101 | select convert(varchar, getdate(), 101) | 12/30/2006 |
| 102 | select convert(varchar, getdate(), 102) | 2006.12.30 |
| 103 | select convert(varchar, getdate(), 103) | 30/12/2006 |
| 104 | select convert(varchar, getdate(), 104) | 30.12.2006 |
| 105 | select convert(varchar, getdate(), 105) | 30-12-2006 |
| 106 | select convert(varchar, getdate(), 106) | 30 Dec 2006 |
| 107 | select convert(varchar, getdate(), 107) | Dec 30, 2006 |
| 110 | select convert(varchar, getdate(), 110) | 12-30-2006 |
| 111 | select convert(varchar, getdate(), 111) | 2006/12/30 |
| TIME FORMATS | ||
| 8 or 108 | select convert(varchar, getdate(), 8) | 00:38:54 |
| 9 or 109 | select convert(varchar, getdate(), 9) | Dec 30 2006 12:38:54:840AM |
| 14 or 114 | select convert(varchar, getdate(), 14) | 00:38:54:840 |
You can also format the date or time without dividing characters, as well as concatenate the date and time string:
| Sample statement | Output |
| select replace(convert(varchar, getdate(),101),'/','') | 12302006 |
| select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') | 12302006004426 |
No comments:
Post a Comment