How to take the date part of SQL

Source: Internet
Author: User
Tags time zones odbc

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.