The syntax of the CAST () function is as follows:
CAST () (<expression> AS <data _ type> [length])
The CONVERT () function syntax is as follows:
CONVERT () (<data _ type> [length], <expression> [, style])
Select cast (100 + 99 as char) convert (varchar (12), getdate ())
The running result is as follows:
------------------------------------------
199 Jan 15 2000
---- Date function ----
DAY () -- The function returns the date value in date_expression.
MONTH () -- The function returns the MONTH value in date_expression.
YEAR () -- returns the YEAR Value in date_expression.
DATEADD (<datepart>, <number>, <date>)
-- The function returns the new date generated by the specified date plus the specified additional date interval number.
DateA parameter is a valid date expression.NumberIs the number of intervals you want to add. For the future time, this number is a positive number, and for the past time, this number is a negative number.
DatepartParameters can be the following values:
Datepart |
Abbreviations |
Year |
Yy, yyyy |
Quarter |
Qq, q |
Month |
Mm, m |
Day of the year |
Dy, y |
Day |
Dd, d |
Week |
Wk, ww |
Week |
Dw, w |
Hours |
Hh |
Minutes |
Mi, n |
Seconds |
Ss, s |
Millisecond |
MS |
Subtle |
Mcs |
Nanoseconds |
Ns |
DATEDIFF (<datepart >,< number >,< date>)
-- The function returns the datepart differences between two specified dates.
DATENAME (<datepart>, <date>) -- The function returns the specified part of the date in the form of a string.
DATEPART (<datepart >,< date>) -- The function returns the specified part of the date in the form of an integer.
GETDATE () -- The function returns the current date and time of the system in the default format of DATETIME.
---- System functions ----
APP_NAME () -- function returns the name of the currently executed application
COALESCE () -- function returns the value of the first non-NULL expression among multiple expressions.
COL_LENGTH (<'table _ name' >,< 'column _ name'>) -- returns the length value of the specified field in the table.
COL_NAME (<table_id >,< column_id>) -- the name of the specified field in the function return table is the column name.
DATALENGTH () -- the actual length of the data returned by the function
DB_ID (['database _ name']) -- number of the database returned by the function
DB_NAME (database_id) -- Name of the database returned by the function
HOST_ID () -- The function returns the name of the server computer.
HOST_NAME () -- returns the name of the server computer.
IDENTITY (<data_type> [, seed increment]) [AS column_name])
-- The IDENTITY () function is used only in the select into statement to insert an identity column to the new table.
/* Select identity (int, 1, 1) as column_name
Into newtable
From oldtable */
ISDATE () -- determines whether the given expression is a reasonable date.
ISNULL (<check_expression>, <replacement_value>) -- The function returns NULL in the expression.
Replace the value with the specified value.
ISNUMERIC () -- The function determines whether the given expression is a reasonable value.
NEWID () -- The function returns a value of the UNIQUEIDENTIFIER type.
NULLIF (<expression1>, <expression2>)
-- If the NULLIF function is equal to expression1 and expression2, NULL is returned.
If the values are not equal, the following syntax is returned for the CAST () function.
CAST () (<expression> AS <data _ type> [length])
The CONVERT () function syntax is as follows:
CONVERT () (<data _ type> [length], <expression> [, style])
Select cast (100 + 99 as char) convert (varchar (12), getdate ())
The running result is as follows:
------------------------------------------
199 Jan 15 2000
---- Date function ----
DAY () -- The function returns the date value in date_expression.
MONTH () -- The function returns the MONTH value in date_expression.
YEAR () -- returns the YEAR Value in date_expression.
DATEADD (<datepart>, <number>, <date>)
-- The function returns the new date generated by the specified date plus the specified additional date interval number.
DATEDIFF (<datepart >,< number >,< date>)
-- The function returns the datepart differences between two specified dates.
DATENAME (<datepart>, <date>) -- The function returns the specified part of the date in the form of a string.
DATEPART (<datepart >,< date>) -- The function returns the specified part of the date in the form of an integer.
GETDATE () -- The function returns the current date and time of the system in the default format of DATETIME.
---- System functions ----
APP_NAME () -- function returns the name of the currently executed application
COALESCE () -- function returns the value of the first non-NULL expression among multiple expressions.
COL_LENGTH (<'table _ name' >,< 'column _ name'>) -- returns the length value of the specified field in the table.
COL_NAME (<table_id >,< column_id>) -- the name of the specified field in the function return table is the column name.
DATALENGTH () -- the actual length of the data returned by the function
DB_ID (['database _ name']) -- number of the database returned by the function
DB_NAME (database_id) -- Name of the database returned by the function
HOST_ID () -- The function returns the name of the server computer.
HOST_NAME () -- returns the name of the server computer.
IDENTITY (<data_type> [, seed increment]) [AS column_name])
-- The IDENTITY () function is used only in the select into statement to insert an identity column to the new table.
/* Select identity (int, 1, 1) as column_name
Into newtable
From oldtable */
ISDATE () -- determines whether the given expression is a reasonable date.
ISNULL (<check_expression>, <replacement_value>) -- The function returns NULL in the expression.
Replace the value with the specified value.
ISNUMERIC () -- The function determines whether the given expression is a reasonable value.
NEWID () -- The function returns a value of the UNIQUEIDENTIFIER type.
NULLIF (<expression1>, <expression2>)
-- If the NULLIF function is equal to expression1 and expression2, NULL is returned.
Returns the value of expression1 if the value is not equal.