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