Sqlserver_ Time Usage

Source: Internet
Author: User

function Description
GETDATE () Returns the current date and time
DATEPART () Returns a separate part of the date/time
DATEADD () Add or subtract a specified time interval in a date
DATEDIFF () Returns a time between two dates
CONVERT () Display Date/time in a different format

SQL Date Data type

MySQL stores date or date/time values in the database using the following data types:

    • DATE-Format: YYYY-MM-DD
    • DATETIME-format: Yyyy-mm-dd HH:MM:SS
    • TIMESTAMP-format: Yyyy-mm-dd HH:MM:SS
    • Year-format: YYYY or YY

SQL Server uses the following data types to store date or date/time values in the database:

    • DATE-Format: YYYY-MM-DD
    • DATETIME-format: Yyyy-mm-dd HH:MM:SS
    • smalldatetime-format: Yyyy-mm-dd HH:MM:SS
    • TIMESTAMP-Format: Unique number

Note: When you create a new table in the database, you need to select a data type for the column!

Definition and usage

1. The DATEPART () function is used to return a separate part of the date/time, such as year, month, day, hour, minute, and so on.

Grammar DATEPART (datepart,date)

The date parameter is a valid day expression. The datepart parameter can be the following value:

2. The DATEADD () function adds or subtracts the specified time interval from the date.

Grammar DATEADD (datepart,number,date)

The date parameter is a valid day expression. The number is the number of intervals you want to add, and for the future time, this is positive, and for the past time, this number is negative.

3. The DATEDIFF () function returns the number of days between two dates.

GrammarDATEDIFF (datepart,startdate,enddate)

The StartDate and EndDate parameters are valid date expressions. The datepart parameter can be the following value:

4. The convert () function is a general function that converts a date to a new data type.

The CONVERT () function can display date/time data in different formats.

Grammar CONVERT ( data_type (length), expression, style)
value Description
Data_type (length) Specifies the target data type (with an optional length).
Expression Specifies the value that needs to be converted.
Style Specifies the date/time of the output format.
The following table shows the style values that are converted to character data by datetime or smalldatetime:

Value
(Century yy)
value
(Century yyyy)
Input/Output Standard
- 0 or 100 Mon dd yyyy hh:miam (or PM) Default
1 101 Mm/dd/yy USA
2 102 Yy.mm.dd Ansi
3 103 Dd/mm/yy British/french
4 104 Dd.mm.yy German
5 105 Dd-mm-yy Italian
6 106 DD Mon yy
7 107 Mon DD, yy
8 108 Hh:mm:ss
- 9 or 109 Mon dd yyyy hh:mi:ss:mmmAM (or PM) Default+millisec
10 110 Mm-dd-yy USA
11 111 Yy/mm/dd Japan
12 112 Yymmdd Iso
- 113 or DD Mon yyyy hh:mi:ss:mmm (24h)
14 114 Hh:mi:ss:mmm (24h)
- 120 or Yyyy-mm-dd Hh:mi:ss (24h)
- or 121 Yyyy-mm-dd hh:mi:ss.mmm (24h)
- 126 Yyyy-mm-ddthh:mi:ss.mmm (no spaces) ISO8601
- 130 DD Mon yyyy hh:mi:ss:mmmAM Hijiri
- 131 Dd/mm/yy Hh:mi:ss:mmmAM Hijiri

DatePart Abbreviations
Years YY, yyyy
Quarter QQ, Q
Month MM, M
The day of the year Dy, y
Day DD, D
Week WK, WW
Week DW, W
Hours hh
Minutes MI, n
Seconds SS, S
Milliseconds Ms
Subtle MCs
Na-Sec Ns

Sqlserver_ Time Usage

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.