SQL Server Functions

Use of some functions in sqlserver (05:

I. Time functions:

DateAdd ([year, Quarter, month, day], number, date)

Function Description: used for addition or subtraction within a specific time range

Parameter description: [year, Quarter, month, and day] is the benchmark unit for the operation, year, Quarter, month, day...

Number: operand. It is positive and negative is reduced as follows:

DataAdd (year, 10, getdate (): returns the year 10 years after the current time.

DataAdd (month,-2, getdate (): returns the month two months before the current time

Datename ([year, Quarter, month, day], date)

Function Description: obtains the year, month, day, and week of a specific time. The type is int.

Parameter description: [year, Quarter, month, and day] is the benchmark unit of the operation. For example:

DateName (year, getdate (): returns the year of the current time.

Year (date): returns the year of the date, int type
Month (date): returns the month of the date, int type
Day (date): returns the day of the date, int type
Datediff ([year, Quarter, month, day], startTime, endTime)

Function Description: returns a specific date difference.

Parameter description: [year, Quarter, month, day], year, month, day, etc,

StartTime is the start time.

EndTime is the end time.

The End Time minus the start time is as follows:

DateDiff (year, '2014-6-20 ', '2014-4-5') returns 1 to ignore other values.

GetDate (): Get the current system time

IsDate (date) is a valid date. If yes, 1 is returned. If no, 0 is returned.

Set datefirst 1-7; SET the day of the week of the current database to the first day,
Print @ DATEFIRST: Check the day of the week on the first day of the current database.

Ii. conversion functions

Convert function:

Ronvert (data_type (len), operobj, style)

Parameter description: the type to be converted by data_type (len) with an optional length,

Operobj: target to be operated,

Style: Specifies the output format of the date/time (as follows :)

Style ID	Style format
100 or 0	Mon dd yyyy hh: miAM (or PM)
101	Mm/dd/yy
102	Yy. mm. dd
103	Dd/mm/yy
104	Dd. mm. yy
105	Dd-mm-yy
106	Dd mon yy
107	Mon dd, yy
108	Hh: mm: ss
109 or 9	Mon dd yyyy hh: mi: ss: mmmAM (or PM)
110	Mm-dd-yy
111	Yy/mm/dd
112	Yymmdd
113 or 13	Dd mon yyyy hh: mm: ss: mmm (24 h)
114	Hh: mi: ss: mmm (24 h)
120 or 20	Yyyy-mm-dd hh: mi: ss (24 h)
121 or 21	Yyyy-mm-dd hh: mi: ss. mmm (24 h)
126	Yyyy-mm-ddThh: mm: ss. mmm (no space)
130	Dd mon yyyy hh: mi: ss: mmmAM
131	Dd/mm/yy hh: mi: ss: mmmAM

Cast function:

Cast (operstr as data_style (len ))

Parameter description: operstr object to be operated,

Date_type (len): the type to be converted. The optional length is as follows:

Cast (123 as decimal (123.00) output:

Iii. Replacement Functions

Replace (data, 'Char ', 'char1'): replace an existing character with a certain format. char is the character to be replaced, and char1 is replaced with a character such:

Replace ('2017/06 ','-0', '-') returns 2011/05

Iv. string processing functions

Substring (str, startlen, endlen) string truncation function,
[Str, startlen, endlen]:
Str: string to be operated
Startlen: Start position of the operation
Endlen: position to be intercepted
Lower (str): uppercase to lowercase
Upper (str): lowercase to uppercase
Left (str, len): len units are intercepted from the left.
Right (str, len): len units are intercepted from the right.
LTRIM (str): removes spaces on the left.
RTRIM (str): removes spaces on the right.
CHARINDEX ('Char ', str): returns the first position of a char.

Currently, many of these functions are used, and others are to be discovered;

Who can count the helplessness of life? Please tell me! Don't go back to your own path. Even if something is helpless, at least I have a hard time and happiness! After many years, when I looked up at the sky ~ The blue color still has my deep thoughts for years! Return to the pure smile of the boy called "memories of water" in that season ~ Then we gather our fingers and we have nowhere to escape.

Use of some functions in sqlserver (05:

I. Time functions:

DateAdd ([year, Quarter, month, day], number, date)

Function Description: used for addition or subtraction within a specific time range

Parameter description: [year, Quarter, month, and day] is the benchmark unit for the operation, year, Quarter, month, day...

Number: operand. It is positive and negative is reduced as follows:

DataAdd (year, 10, getdate (): returns the year 10 years after the current time.

DataAdd (month,-2, getdate (): returns the month two months before the current time

Datename ([year, Quarter, month, day], date)

Function Description: obtains the year, month, day, and week of a specific time. The type is int.

Parameter description: [year, Quarter, month, and day] is the benchmark unit of the operation. For example:

DateName (year, getdate (): returns the year of the current time.

Year (date): returns the year of the date, int type
Month (date): returns the month of the date, int type
Day (date): returns the day of the date, int type
Datediff ([year, Quarter, month, day], startTime, endTime)

Function Description: returns a specific date difference.

Parameter description: [year, Quarter, month, day], year, month, day, etc,

StartTime is the start time.

EndTime is the end time.

The End Time minus the start time is as follows:

DateDiff (year, '2014-6-20 ', '2014-4-5') returns 1 to ignore other values.

GetDate (): Get the current system time

IsDate (date) is a valid date. If yes, 1 is returned. If no, 0 is returned.

Set datefirst 1-7; SET the day of the week of the current database to the first day,
Print @ DATEFIRST: Check the day of the week on the first day of the current database.

Ii. conversion functions

Convert function:

Ronvert (data_type (len), operobj, style)

Parameter description: the type to be converted by data_type (len) with an optional length,

Operobj: target to be operated,

Style: Specifies the output format of the date/time (as follows :)

Style ID	Style format
100 or 0	Mon dd yyyy hh: miAM (or PM)
101	Mm/dd/yy
102	Yy. mm. dd
103	Dd/mm/yy
104	Dd. mm. yy
105	Dd-mm-yy
106	Dd mon yy
107	Mon dd, yy
108	Hh: mm: ss
109 or 9	Mon dd yyyy hh: mi: ss: mmmAM (or PM)
110	Mm-dd-yy
111	Yy/mm/dd
112	Yymmdd
113 or 13	Dd mon yyyy hh: mm: ss: mmm (24 h)
114	Hh: mi: ss: mmm (24 h)
120 or 20	Yyyy-mm-dd hh: mi: ss (24 h)
121 or 21	Yyyy-mm-dd hh: mi: ss. mmm (24 h)
126	Yyyy-mm-ddThh: mm: ss. mmm (no space)
130	Dd mon yyyy hh: mi: ss: mmmAM
131	Dd/mm/yy hh: mi: ss: mmmAM

Cast function:

Cast (operstr as data_style (len ))

Parameter description: operstr object to be operated,

Date_type (len): the type to be converted. The optional length is as follows:

Cast (123 as decimal (123.00) output:

Iii. Replacement Functions

Replace (data, 'Char ', 'char1'): replace an existing character with a certain format. char is the character to be replaced, and char1 is replaced with a character such:

Replace ('2017/06 ','-0', '-') returns 2011/05

Iv. string processing functions

Substring (str, startlen, endlen) string truncation function,
[Str, startlen, endlen]:
Str: string to be operated
Startlen: Start position of the operation
Endlen: position to be intercepted
Lower (str): uppercase to lowercase
Upper (str): lowercase to uppercase
Left (str, len): len units are intercepted from the left.
Right (str, len): len units are intercepted from the right.
LTRIM (str): removes spaces on the left.
RTRIM (str): removes spaces on the right.
CHARINDEX ('Char ', str): returns the first position of a char.

Currently, many of these functions are used, and others are to be discovered;