Asp.net SQL server date and time format CONVERT conversion

Asp tutorial. net SQL server date and time format convert conversion
On the client, you can use the tostring () method for formatting:

Datetime. now. tostring ("d") returns the standard date format.
Datetime. now. tostring ("mm dd, yy") returns the Chinese date format: April 13
Datetime. now. tostring ("hh: mm") return time:

Parameters can be freely combined to meet the requirements.

Format when binding:

<% # Eval ("transaction time", "{0: mm dd, yyyy}"). tostring () %>

<Asp: boundfield datafield = "msgtime" dataformatstring = "{0: mm dd, yyyy}" headertext = "date" htmlencode = "false"/>

The htmlencode attribute must be false. Otherwise, the dataformatstring attribute does not take effect. (The same applies to other formatting methods)

SQL server database tutorial-side formatting:

Execute SQL

Select convert (varchar (100), field name, 0) from table name

Query result 05 16 2006 am

You only need to change (varchar (100), field name, 0) to 0 to get any other SQL built-in style

The following are available date and time formats:

 

Select convert (varchar (100), getdate (), 0): 05 16 2006 am
Select convert (varchar (100), getdate (), 1): 05/16/06
Select convert (varchar (100), getdate (), 2): 06.05.16
Select convert (varchar (100), getdate (), 3): 16/05/06
Select convert (varchar (100), getdate (), 4): 16.05.06
Select convert (varchar (100), getdate (), 5): 16-05-06
Select 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: 827am
Select convert (varchar (100), getdate (), 10): 05-16-06
Select 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): 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 am
Select convert (varchar (100), getdate (), 23):
Select convert (varchar (100), getdate (), 24): 10:57:47
Select convert (varchar (100), getdate (), 25): 10:57:47. 250
Select convert (varchar (100), getdate (), 100): 05 16 2006 am
Select convert (varchar (100), getdate (), 101): 05/16/2006
Select convert (varchar (100), getdate (), 102): 2006.05.16
Select convert (varchar (100), getdate (), 103): 16/05/2006
Select convert (varchar (100), getdate (), 104): 16.05.2006
Select convert (varchar (100), getdate (), 105): 16-05-2006
Select convert (varchar (100), getdate (), 106): 16 05 2006
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: 437am
Select convert (varchar (100), getdate (), 110): 05-16-2006
Select convert (varchar (100), getdate (), 111): 2006/05/16
Select convert (varchar (100), getdate (), 112): 20060516
Select convert (varchar (100), getdate (), 113): 16 05 2006 10: 57: 49: 513
Select convert (varchar (100), getdate (), 114): 10: 57: 49: 547
Select convert (varchar (100), getdate (), 120): 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: 907am

Several common formats that can be recognized:

D. A single-digit date has no leading zero.
A day in dd Month. A single-digit date has a leading zero.

M month number. One-digit month does not have a leading zero.
Mm month number. A single-digit month has a leading zero.
Yy does not contain the year of the epoch. If the year that does not contain the epoch is less than 10, the year with the leading zero is displayed.
Yyyy includes the four-digit year of the epoch.

H 12-hour. One-digit hours do not have a leading zero.
The hour in hh 12-hour format. The number of hours in a single digit has a leading zero.
H in 24-hour format. One-digit hours do not have a leading zero.
The hour in the 24-hour format. The number of hours in a single digit has a leading zero.
M minutes. The number of minutes in a single digit does not start with zero.
Mm minutes. The number of minutes in a single digit has a leading zero.
S seconds. The number of seconds in a single digit does not have a leading zero.
Ss seconds. The number of seconds in a single digit has a leading zero.

Convert function usage
-- Production Time: 2008/09/18

Format:
Convert (data_type, expression [, style])

Note:
(Datetime, smalldatetime) and string type (nchar, nvarchar, char, varchar) are converted to each other before the three parameters of the function are used,
The first parameter is the converted size. The second parameter is the converted date field or function, and the second parameter is the converted format. The details are as follows:
0 | 0 or 100 | mon dd yyyy hh: miam (or pm)
1 | 101 | mm/dd/yy
2 | 102 | yy-mm-dd
3 | 103 | dd/mm/yy
4 | 104 | dd-mm-yy
5 | 105 | dd-mm-yy
6 | 106 | dd mon yy
7 | 107 | mon dd, yy
Eight | 108 | hh: mm: ss
9 | 9 or 109 | mon dd yyyy hh: mi: ss: mmmmam (or pm)
10 | 110 | mm-dd-yy
11 | 111 | yy/mm/dd
12 | 112 | yymmdd
11 | 13 or 113 | dd mon yyyy hh: mi: ss: mmm (in 24-hour format)
14 | 114 | hh: mi: ss: mmm (in 24-hour format)
15 | 20 or 120 | yyyy-mm-dd hh: mi: ss (in 24-hour format)
16 | 21 or 121 | yyyy-mm-dd hh: mi: ss: mmm (in 24-hour format)
Example:
Select convert (varchar (100), getdate (), 0): 05 16 2006 am
Select convert (varchar (100), getdate (), 1): 05/16/06
Select convert (varchar (100), getdate (), 2): 06.05.16
Select convert (varchar (100), getdate (), 3): 16/05/06
Select convert (varchar (100), getdate (), 4): 16.05.06
Select convert (varchar (100), getdate (), 5): 16-05-06
Select 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: 827am
Select convert (varchar (100), getdate (), 10): 05-16-06
Select 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): 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 am
Select convert (varchar (100), getdate (), 23):
Select convert (varchar (100), getdate (), 24): 10:57:47
Select convert (varchar (100), getdate (), 25): 10:57:47. 250
Select convert (varchar (100), getdate (), 100): 05 16 2006 am
Select convert (varchar (100), getdate (), 101): 05/16/2006
Select convert (varchar (100), getdate (), 102): 2006.05.16
Select convert (varchar (100), getdate (), 103): 16/05/2006
Select convert (varchar (100), getdate (), 104): 16.05.2006
Select convert (varchar (100), getdate (), 105): 16-05-2006
Select convert (varchar (100), getdate (), 106): 16 05 2006
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: 437am
Select convert (varchar (100), getdate (), 110): 05-16-2006
Select convert (varchar (100), getdate (), 111): 2006/05/16
Select convert (varchar (100), getdate (), 112): 20060516
Select convert (varchar (100), getdate (), 113): 16 05 2006 10: 57: 49: 513
Select convert (varchar (100), getdate (), 114): 10: 57: 49: 547
Select convert (varchar (100), getdate (), 120): 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: 907am
Select convert (varchar (100), getdate (), 131): 18/04/1427 10: 57: 49: 920am