I. Convert
CONVERT (varchar), GETDATE (), +): varchar (10) The number of intercept bits can be adjusted, can display up to 19 bits (varchar (19))
such as: 2009-12-12 10:10:10-2009-12-12
no century digits (yy) (1) |
|
standard |
input/output ( Span class= "sup" >3) |
- |
0 or (1, 2) |
Default |
Mon dd yyyy hh:miam (or PM) |
1 |
101 |
United States |
Mm/dd/yyyy |
2 |
102 |
Ansi |
Yy.mm.dd |
3 |
103 |
UK/France |
Dd/mm/yyyy |
4 |
104 |
Germany |
Dd.mm.yy |
5 |
105 |
Italy |
Dd-mm-yy |
6 |
106 (1) |
- |
DD Mon yy |
7 |
107 (1) |
- |
Mon dd, yy |
8 |
108 |
- |
Hh:mi:ss |
- |
9 or 109 (1, 2) |
Default setting + milliseconds |
Mon dd yyyy hh:mi:ss:mmmAM (or PM) |
10 |
110 |
United States |
Mm-dd-yy |
11 |
111 |
Japan |
Yy/mm/dd |
12 |
112 |
Iso |
Yymmdd Yyyymmdd |
- |
or 113 (1, 2) |
Europe default setting + milliseconds |
DD Mon yyyy hh:mi:ss:mmm (24h) |
14 |
114 |
- |
Hh:mi:ss:mmm (24h) |
- |
or ( 2) |
ODBC specification |
Yyyy-mm-dd Hh:mi:ss (24h) |
- |
or 121 (2) |
ODBC specification (with milliseconds) |
Yyyy-mm-dd hh:mi:ss.mmm (24h) |
- |
126 (4) |
ISO8601 |
Yyyy-mm-ddthh:mi:ss.mmm (no spaces) |
- |
127 (6, 7) |
The ISO8601 with time zone Z. |
Yyyy-mm-ddthh:mi:ss.mmmz (No spaces) |
- |
(1, 2) |
Hijri (5) |
DD Mon yyyy hh:mi:ss:mmmAM |
- |
131 (2) |
Hijri (5) |
Dd/mm/yy Hh:mi:ss:mmmAM |
1 These style values will return an indeterminate result. Includes all (yy) (without century digits) style and part (yyyy) (with century digit) style.
2 default values (style 0 or 9 or 109, or 113, or 121) always returns century digits (yyyy).
3 input when converting to datetime , or output when converting to character data.
4 is designed for use with XML. For conversions from datetime or smalldatetime to character data, the output format is described in the previous table.
The 5 calendar is a system of calendars with multiple variants. SQL Server uses the Kuwaiti algorithm.
Important NOTES: |
By default, SQL Server interprets two-digit years based on the cutoff year 2049. In other words, the two-digit year 49 is interpreted as 2049, and the two-digit year 50 is interpreted as 1950. Many client applications, such as automation object-based applications, use the cutoff year 2030. SQL Server provides a "two-digit year cutoff" configuration option that allows you to change the cutoff year used by SQL Servers to consistently process dates. We recommend that you specify a four-digit year. |
6 only supports conversion from character data to datetime or smalldatetime. When character data that represents only a date or time component is converted to a datetime or smalldatetime data type, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.
7 The optional Time region indicator (Z) makes it easier to map an XML datetime value with time zone information to a SQL Server datetime value that does not have a time zone. Z is an indicator of the time zone UTC-0. Other time zones are indicated by a hh:mm offset of + or-direction. For example: 2006-12-12T23:45:12-08:00
.
When converting from smalldatetime to character data, styles containing seconds or milliseconds will display zeros at these locations. When converting from a datetime or smalldatetime value using the appropriate char or varchar data type length, you can truncate the unwanted date part.
Two. DatePart returns an integer representing the specified date portion of the specified date. Grammar
DATEPART ( datepart ,date )
Parameters
DatePartis a parameter that specifies the date part that should be returned. The following table lists the date parts and abbreviations identified by Microsoft®sql Server™.
Date Part |
Abbreviations |
Year |
YY, yyyy |
Quarter |
QQ, Q |
Month |
MM, M |
DayOfYear |
Dy, y |
Day |
DD, D |
Week |
WK, WW |
Weekday |
Dw |
Hour |
hh |
Minute |
MI, n |
Second |
SS, S |
Millisecond |
Ms |
Week(
wk, wwThe date part reflects the changes made to the SET Datefirst. The January 1 of any year defines the
WeekThe start number of the date part, for example: DATEPART (
wk, ' Jan 1, xxxx ') = 1, here XXXX represents any one year.
Weekday(
DWThe date part returns the number of days that correspond to the day of the week, for example: Sunday = 1, Saturday = 7.
WeekdayThe number of date parts produced depends on the value set by Set Datefirst, which sets the first day of the week.
Dateis to return
datetimeOr
smalldatetimeAn expression of a value or date format string. For dates after January 1, 1753
datetimeThe data type. The earlier date is stored as character data. When the input
datetimeValue, always enclose it in quotation marks. Because
smalldatetimeOnly accurate to minutes, so when using
smalldatetimeValue, the seconds and milliseconds are always 0. If you specify only the last two digits of the year, the number of the last two digits of the value that is less than or equal to the "two-digit year cutoff" configuration option is the same century as the year in which the cutoff is located. The number of the last two digits of the value of the option is greater than the century of the first century in which the cutoff year was located. For example, if
Digit year cutoffIs 2049 (the default), then 49 is interpreted as 2049,2050 is interpreted as 1950. To avoid blurring, use a four-digit year.
How to take the date part of SQL