-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse -- Syntax for CAST: CAST ( expression AS data_type [ ( length ) ] ) -- Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) can be one of the values shown in the following table. For example, the value '2012-11-07T.000 is displayed as '2012-11-07T'.yyyy-mm-dd Thh:mi:Z (no spaces) Note: When the value for milliseconds (mmm) is 0, the milliseconds value is not displayed.
Beginning with SQL Server 2012, the only styles that are supported when converting from date and time types to datetimeoffset are 0 or 1. SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.yyyy-mm-dd Thh:mi:(no spaces) Note: When the value for milliseconds (mmm) is 0, the millisecond value is not displayed.
For example, the value '2012-11-07T.000 is displayed as '2012-11-07T'.
That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950.
Many client applications, such as those based on Automation objects, use a cutoff year of 2030.
SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. Only supported when casting from character data to datetime or smalldatetime.
When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to .000, and the unspecified date component is set to 1900-01-01.
The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Other time zones are indicated with HH: MM offset in the + or - direction. When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions.
You can truncate unwanted date parts when you convert from datetime or smalldatetime values by using an appropriate char or varchar data type length.
When you convert to datetimeoffset from character data with a style that includes a time, a time zone offset is appended to the result.
Use default parsing behavior that discards insignificant white space and does not allow for an internal DTD subset.
Note: When you convert to the xml data type, SQL Server insignificant white space is handled differently than in XML 1.0.
For more information, see Create Instances of XML Data. If enabled, the server can use the following information that is provided in an internal DTD subset to perform nonvalidating parse operations. -Internal entity references are resolved and expanded.
-The DTD content model will be checked for syntactical correctness. It also does not evaluate the XML declaration to see whether the standalone attribute is set yes or no, but instead parses the XML instance as if it is a stand-alone document.