Date and Text Functions of common SQL Functions

Date Functions

Datetime functions are often used to operate datetime and smalldatetime data.

 

(1) Day (<date_expression>)

The day function returns the date value in date_expression.

Example:Select Day('2017-10-15')Return Value:15

 

(2) month (<date_expression>)

The month function returns the month value in date_expression.

Example:Select Month('2017-10-15')Return Value:10

 

(3) Year (<date_expression>)

The year function returns the Year Value in date_expression.

Example:Select Year('2017-10-15')Return Value:2004

 

(4) dateadd (<datepart> <number> <date>)

The dateadd function returns the new date generated by the specified date plus the specified additional date (of the datepart type.

Example:Select Dateadd(Day,2,'2017-10-15')Return Value:2004-10-17 00:00:00. 000

Select Dateadd(Hour,2,'2017-10-15')Return Value:2004-10-15 02:00:00. 000

Note: ParametersDatepartThis function is often used in date functions to specify conditions for each group of data of the date type, such as year, season, month, day, and week. The values are as follows:

Value of the datepart parameter in the date function
Datepart	Abbreviations	Value
Year	YY, yyyy	1753 ~ 9999
Quarter	QQ, Q	1 ~ 4
Month	Mm, m	1 ~ 12
Day of year	Dy, y	1 ~ 366
Day	DD, d	1 ~ 31
Week	Wk, WW	1 ~ 54
Weekday	DW	1 ~ 7
Hour	HH	0 ~ 23
Minute	Mi, n	0 ~ 59
Second	SS, S	0 ~ 59
Millisecond	MS	0 ~ 999

 

(5) datediff (<datepart>, <date1>, <date2>)

The datediff function returns the difference between two specified dates in datepart, that is, the difference between date2 and date1 is greater than that of date1. The result value is an integer with a plus or minus sign.

Example:Select Datediff(Day,'2017-09-01','2017-09-18')Return Value:17

Note: For different datepart, the maximum difference value allowed by the datediff function is different. For example, if the datepart is second, the maximum difference allowed by the datediff function is 68 years. If the datepart is millisecond, the maximum difference allowed is 24 days, 20 hours, 30 minutes, 23 seconds, 647 milliseconds.

 

(6) datename (<datepart>, <date>)

The datename function usesStringReturn the specified part of the date, which is specified by datepart.

Example:Select Datename(Weekday,'2017-10-15')Back: Friday

Select Datename(Year ,'2017-10-15')Return Value:2004

 

(7) datepart (<datepart>, <date>)

The datepart function usesIntegerReturn the specified part of the date (similar to datename (<datepart>, <date>)

), Which is specified by datepart.

Note: datepart (day, <date>) is equivalent to day (<date>)

Datepart (month, <date>) is equivalent to month (<date>)

Datepart (year, <date>) is equivalent to year (<date>)

 

(8) getdate ()

The getdate function returns the current date and time of the system in the default format of datetime. It is often used as a parameter of other functions or commands.

Example:Select Getdate()Return Value:2013-04-1821: 42: 03.837

Text and image Functions

(1) textptr (<column>)

The textptr function returns a pointer to the first database page storing text.

Note: The returned value is a binary string of the varbinary (16) type. If the column whose data type is text, ntext, or image does not have an initial value, the textptr function returns a null pointer.

 

(2) textvalid (<'table. column'>, <text _ pointer>)

The textvalid function is used to check whether the specified text pointer is valid. If the value is valid, 1 is returned. If the value is invalid, 0 is returned. If the column does not assign an initial value, null is returned.