SQL statement format Query

 

Replace (convert (char (50), getdate (), 111 ),'/','-')

Parse this sentence:

The string to be searched. Original-string: Convert (char (50), getdate (), 111)

String to be searched and replaced by replace-string. Search-string :'/'

This string is used to replace search-string. Replace-string :'-'

In SQL, you can use the replace function to replace certain characters in a field. The syntax is as follows:

Syntax
Replace (original-string, search-string, replace-string)

Parameters
If a parameter is null, this function returns NULL.

The string to be searched by original-string. Can be of any length.

The string to be searched and replaced by replace-string. The length of the string cannot exceed 255 bytes. If search-string is a null string, the original string is returned as is.

Replace-string this string is used to replace search-string. Can be of any length. If replacement-string is a Null String, all search-strings that appear are deleted.

Example:
Update tablename set recordname = Replace (recordname, 'abc', 'ddd ')
Replace ABC in the recordname field of tablename with DDD.

This function does not support text. ntext fields can be replaced by the following statement:
Update tablename set recordname = Replace (cast (recordname as varchar (8000), 'abc', 'ddd ')
Use the cast function to convert the content of the text field to the varchar type and then replace it.

 

Convert the date format using the convert function in sqlserver

Why should I replace the "/" character in convert with "-"? It is because the convert format is: 2007/05/22
Note that the time format of convert is in the format of 0 before it is formatted. For example, the result of 12:12:31 is:
Therefore, before querying, you must add a leading 0 to the number in the month and day of your date, which is less than 10 (not including 10,
For example, 1 should be 01.

How to Use convert:

Format:

Convert (data_type, expression, [style])

 

Note: This style is generally used only when the time type (datetime, smalldatetime) and string type (nchar, nvarchar, Char, varchar) are converted to each other.

 

Example:

Select convert (varchar (30), getdate (), 101) now
Result:

Now

09/15/2001

The conversion time of a style number is as follows: style (two bits represent the year) style (four bits represent the year) Input/Output Format
0 100 mon dd yyyy hh: miam (or pm)
1 101/DD/yy
2 102 yymmdd
3 103 dd/mm/yy
4 104 ddmmyy
5 105 ddmmyy
6 106 dd mon YY
7 107 mon DD, YY
8 108 hh: mm: SS
9 109 mon dd yyyy hh: MI: SS: mmmmam (or pm)
10 110 mmddyy
11 111 YY/MM/dd
12 112 yymmdd
13 113 dd mon yyyy hh: MI: SS: Mmm (in 24-hour format)
14 114 hh: MI: SS: Mmm (in 24-hour format)
20 120 yyyymmdd hh: MI: SS (in 24-hour format)
21 121 yyyymmdd hh: MI: SS: Mmm (in 24-hour format)