Example of date formatting in SQL

date formatting example in SQL:,

Select convert (varchar (100), getdate (), 0): 05 16 2006 10: 57 amselect convert (varchar (100), getdate (), 1 ): 05/16/06 select convert (varchar (100), getdate (), 2): 06.05.16select convert (varchar (100), getdate (), 3 ): 16/05/06 select convert (varchar (100), getdate (), 4): 16.05.06select convert (varchar (100), getdate (), 5 ): 16-05-06select convert (varchar (100), getdate (), 6): 16 05 06 select convert (varchar (100), Getdate (), 7): 05 16, 06 select convert (varchar (100), getdate (), 8): 10: 57: 46 select convert (varchar (100 ), getdate (), 9): 05 16 2006 10: 57: 46: 827 amselect convert (varchar (100), getdate (), 10): 05-16-06select convert (varchar (100 ), getdate (), 11): 06/05/16 select convert (varchar (100), getdate (), 12): 060516 select convert (varchar (100), getdate (), 13): 16 05 2006 10: 57: 46: 937 select convert (varchar (100 ), Getdate (), 14): 10: 57: 46: 967 select convert (varchar (100), getdate (), 20): 2006-05-16 10: 57: 47 select convert (varchar (100), getdate (), 21): 10:57:47. 157 select convert (varchar (100), getdate (), 22): 05/16/06 10:57:47 amselect convert (varchar (100), getdate (), 23 ): 2006-05-16select convert (varchar (100), getdate (), 24): 10: 57: 47 select convert (varchar (100), getdate (), 25): 10:57:47. 2 50 select convert (varchar (100), getdate (), 100): 05 16 2006 10: 57 amselect convert (varchar (100), getdate (), 101 ): 05/16/2006 select convert (varchar (100), getdate (), 102): 2006.05.16select convert (varchar (100), getdate (), 103): 16/05/2006 select convert (varchar (100 ), getdate (), 104): 16.05.2006select convert (varchar (100), getdate (), 105): 16-05-2006select convert (varchar (100), getdate (), 106): 16 05 20 06 select convert (varchar (100), getdate (), 107): 05 16,200 6 select convert (varchar (100), getdate (), 108): 10: 57: 49 select convert (varchar (100), getdate (), 109): 05 16 2006 10: 57: 49: 437 amselect convert (varchar (100), getdate (), 110): 05-16-2006select convert (varchar (100), getdate (), 111): 2006/05/16 select convert (varchar (100), getdate (), 112 ): 20060516 select convert (varchar (100), getdate (), 113): 16 0 5 2006 10: 57: 49: 513 select convert (varchar (100), getdate (), 114): 10: 57: 49: 547 select convert (varchar (100 ), getdate (), 120): 2006-05-16 10: 57: 49 select convert (varchar (100), getdate (), 121): 10:57:49. 700 select convert (varchar (100), getdate (), 126): 2006-05-16t10: 57: 49.827 select convert (varchar (100), getdate (), 130 ): 18 ???? ?????? 1427 10: 57: 49: 907 amselect convert (varchar (100), getdate (), 131): 18/04/1427 10: 57: 49: 920am

Commonly used:

 
Select convert (varchar (100), getdate (), 8): 10: 57: 46 select convert (varchar (100), getdate (), 24): 10: 57: 47 select convert (varchar (100), getdate (), 108): 10: 57: 49 select convert (varchar (100), getdate (), 12 ): 060516 select convert (varchar (100), getdate (), 23 ):
 

Notes for date formatting:

 

Usage of convert conversion functions in SQL

How to Use convert:

Else ---------------------------------------------------------------------------------------------------------------------------------

Else ---------------------------------------------------------------------------------------------------------------------------------

Format:

Convert (DATa_type, expression [, style])

Note:

This style is generally in the time type (datetime, smalldatetime) and string type (nchar, nvarchar, Char, varchar)

It is used only when they are converted to each other.

example:

select convert (varchar (30), getdate (), 101) Now

the result is

now

skip-

Slave --------------------------------------------------------------------------------------------------------------------------------

09/15/2001

The meaning of the style number in the conversion time is as follows:

Bytes -------------------------------------------------------------------------------------------------

Style (2 bits represent the year) | style (4 bits represent the year) | input/output format

Bytes -------------------------------------------------------------------------------------------------

-| 0 or 100 | mon dd yyyy hh: miam (or pm)

Bytes -------------------------------------------------------------------------------------------------

1 | 101 | mm/DD/yy

Bytes -------------------------------------------------------------------------------------------------

2 | 102 | YY-mm-dd

Bytes -------------------------------------------------------------------------------------------------

3 | 103 | dd/mm/yy

Bytes -------------------------------------------------------------------------------------------------

4 | 104 | DD-mm-yy

Bytes -------------------------------------------------------------------------------------------------

5 | 105 | DD-mm-yy

Bytes -------------------------------------------------------------------------------------------------

6 | 106 | dd mon YY

Bytes -------------------------------------------------------------------------------------------------

7 | 107 | mon DD, YY

Bytes -------------------------------------------------------------------------------------------------

Eight | 108 | hh: mm: SS

Bytes -------------------------------------------------------------------------------------------------

-| 9 or 109 | mon dd yyyy hh: MI: SS: mmmmam (or pm)

Bytes -------------------------------------------------------------------------------------------------

10 | 110 | mm-dd-yy

Bytes -------------------------------------------------------------------------------------------------

11 | 111 | YY/MM/dd

Bytes -------------------------------------------------------------------------------------------------

12 | 112 | yymmdd

Bytes -------------------------------------------------------------------------------------------------

-| 13 or 113 | dd mon yyyy hh: MI: SS: Mmm (in 24-hour format)

Bytes -------------------------------------------------------------------------------------------------

14 | 114 | hh: MI: SS: Mmm (in 24-hour format)

Bytes -------------------------------------------------------------------------------------------------

-| 20 or 120 | yyyy-mm-dd hh: MI: SS (in 24-hour format)

Bytes -------------------------------------------------------------------------------------------------

-| 21 or 121 | yyyy-mm-dd hh: MI: SS: Mmm (in 24-hour format)