In SQL Server, how does one extract the date or time in datetime format data?

Q:
Example:
I want to get
And
What should I do?

______________________________________________________________________________________________
Answer 1:
Format (yourdatetime, "yyyy-m-d ")
Format (yourdatetime, "H: M: s ")
______________________________________________________________________________________________
Answer 2:
How can I display a function that is not recognizable by format? My database is sqlserver
______________________________________________________________________________________________
Answer 3:
Select convert (char (20), col_date, 120) from ....
______________________________________________________________________________________________
Answer 4:
Wrong
Select convert (char (10), col_date, 120) from ....

______________________________________________________________________________________________
Answer 5:
-- Function: Get date from time
Func_getdatefromdatetime (@ date datetime)
Return varchar (10)
Begin
Declare @ date1 varchar (10)

Select @ date1 = convert (char (4), Year (@ date) + '-' + convert (varchar (2), month (@ date )) + '-' + convert (varchar (2), Day (@ date ))

Return @ date1
End
______________________________________________________________________________________________
Answer 6:
Xiahouwen (Active Target. Net) Thanks. Can we extract the date and time?
______________________________________________________________________________________________
Answer 7:

Important by default, SQL Server interprets two-digit years based on the end year 2049. That is, the year 49 with two digits is interpreted as 2049, and the year 50 with two digits is interpreted as 1950. Many client applications (such as client applications based on OLE automation objects) Use 2030 as the end year. SQL Server provides a configuration option ("") to change the end year used by SQL Server and process the date in a consistent manner. However, the safest way is to specify a four-digit year.

When converting from smalldatetime to character data, the style that contains seconds or milliseconds will display zero at these locations. When converting from the datetime or smalldatetime value, you can use the appropriate Char or varchar data type length to cut off the date part that is not required.

The following table shows the style values when converting from float or real to character data.

Value output
0 (default) is a maximum of six digits. Use scientific notation as needed.
1 is always an 8-bit value. Always use scientific notation.
2 is always a 16-bit value. Always use scientific notation.

In the following table, the left column indicates the style value when converting from money or smallmoney to character data.

Value output
0 (default value) each three digits on the left of the decimal point are not separated by commas. Two digits on the right of the decimal point, for example, 4235.98.
1. Each three digits on the left of the decimal point are separated by commas (,). The two digits on the right of the decimal point, for example, 3,510.92.
2. Each three digits on the left of the decimal point are not separated by commas. Four digits are used on the right of the decimal point, for example, 4235.9819.

Return type
Returns the same value as Data Type 0.

Note
Implicit conversions refer to conversions that do not specify cast or convert functions. Explicit conversions refer to conversions of cast (convert) functions that have been specified. The following charts show all explicit and implicit conversions of data types available for the SQL Server System, including bigint and SQL _variant.

This indicates that Unicode data always uses an even number of bytes. Therefore, when converting binary or varbinary data types to Unicode supported data types, a prompt is displayed. For example, this conversion does not return a hexadecimal value of 41, but returns the hexadecimal value of 4100: Select cast (0x41 as nvarchar) as varbinary)

Automatic conversion of text and image data types is not supported. You can explicitly convert text data to character data, and explicitly convert image data to binary or varbinary data, but the maximum length is 8000. If an incorrect conversion is attempted (for example, converting a character expression containing letters to an int), SQL Server generates an error message.

When cast or convert outputs a string and the input is a string, the output has the same sorting rule and sorting rule label as the input. If the input is not a string, the default database sorting rule and the default mandatory sorting rule label are used. For more information, see sorting rule priority.

To assign different sorting rules to the output, apply the Collate clause to the Result Expression of the cast or convert function. For example:

Select cast ('abc' as varchar (5) Collate french_cs_as

There is no implicit conversion from the SQL _variant data type for the assignment, but there is a implicit conversion to SQL _variant.

When converting a character or Binary Expression (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of different data types, the data may be truncated and only a portion of the data is displayed, or an error is returned because the result is too short to be displayed. Except the conversions shown in the following table, conversions to Char, varchar, nchar, nvarchar, binary, and varbinary are truncated.

Result of converting the converted data type to the Data Type
Int, smallint, or tinyint char *
Varchar *
Nchar E
Nvarchar E
Money, smallmoney, numeric, decimal, float, or real char E
Varchar E
Nchar E
Nvarchar E

* The result cannot be displayed because it is too short.
E. An error is returned because the result length is too short to be displayed.

Microsoft SQL Server only ensures round-trip conversion (that is, conversion from the original data type and return the original data type) to produce the same value between versions. The following example shows round-trip conversion:

Declare @ myval decimal (5, 2)
Set @ myval = 193.57
Select cast (@ myval as varbinary (20) as decimal (10, 5 ))
-- Or, using convert
Select convert (decimal (10, 5), convert (varbinary (20), @ myval ))

For example, do not try to Construct binary values and convert them to numeric data types. SQL Server does not guarantee that the results of converting decimal or numeric data type to binary are the same in different versions of SQL Server.

The following example shows the result expression that cannot be displayed because it is too short.

Use pubs
Select substring (title, 1, 25) as title, cast (ytd_sales as char (2 ))
From titles
Where type = 'trad _ cook'

The following is the result set:

Title
---------------------------
Onions, leeks, and garlic *
Fifty years in Buckingham *
Sushi, anyone? *

(3 row (s) affected)

When data types with different decimal places are converted, the value is truncated to the most precise digit. For example, the result of select cast (10.6496 as INT) is 10.

If the number of decimal places of the target data type is smaller than the number of decimal places of the source data type, the value to be converted is rounded down. For example, cast (10.3496847 as money) returns $10.3497.

When converting non-numeric char, nchar, varchar, or nvarchar data to int, float, numeric, or decimal data, SQL Server Returns an error message. When an empty string ("") is converted to numeric or decimal, SQL Server Returns an error message.

Use binary string data
When binary or varbinary data is converted to character data and the odd digit value is specified after X, SQL Server adds 0 (zero) after X to become an even number.

Binary data contains characters ranging from 0 to 9 and from A to F (or from A to F), each of which is a group. The binary string must start with 0x. For example, to enter ff, type 0xff. The maximum value is a binary value of 8000 bytes, and the maximum value of each byte is ff. Binary data types cannot be used for hexadecimal data, but for bit mode. The accuracy of hexadecimal numbers stored as binary data cannot be guaranteed.

When the length of the binary data type is specified, each two characters is counted as a unit length. The length is 10, indicating that 10 double character groups will be entered.

Null binary strings represented by 0x can be stored as binary data.

Example
A. Use both cast and convert
In each example, the name of the retrieved book (the first digit of the current sales of these books is 3) is retrieved, and the ytd_sales of these books is converted to Char (20 ).

-- Use cast.
Use pubs
Go
Select substring (title, 1, 30) as title, ytd_sales
From titles
Where cast (ytd_sales as char (20) Like '3%'
Go

-- Use convert.
Use pubs
Go
Select substring (title, 1, 30) as title, ytd_sales
From titles
Where convert (char (20), ytd_sales) Like '3%'
Go

The following is the result set of any query:

Title ytd_sales
-----------------------------------------
Cooking with computers: surrep 3876
Computer phobic and non-phobic 375
Emotional security: a new algo 3336
Onions, leeks, and garlic: coo 375

(4 row (s) affected)

B. Use cast with Arithmetic Operators
In the following example, the total end-to-end sales (ytd_sales) and the price (price) of each book are separated for separate column calculation (copies ). After rounding to the nearest integer, the result is converted to the int data type.

Use pubs
Go
Select cast (round (ytd_sales/price, 0) as INT) as 'Copies'
From titles
Go

The following is the result set:

Copies
------
205
324
6262
205
102
7440
Null
383
205
Null
17
187
16
204
418
18
1263
273

(18 row (s) affected)

C. Use cast for concatenation
The following example uses the cast data type conversion function to concatenate non-character and non-binary expressions.

Use pubs
Go
Select 'the price is '+ Cast (price as varchar (12 ))
From titles
Where price> 10.00
Go

The following is the result set:

------------------
The price is 19.99
The price is 11.95
The price is 19.99
The price is 19.99
The price is 22.95
The price is 20.00
The price is 21.59
The price is 10.95
The price is 19.99
The price is 20.95
The price is 11.95
The price is 14.99

(12 row (s) affected)

D. Use cast to obtain more readable texts.
In the following example, cast is used in the selection list to convert the title column to the char (50) column, making the results easier to read.

Use pubs
Go
Select cast (title as char (50), ytd_sales
From titles
Where type = 'trad _ cook'
Go

The following is the result set:

Ytd_sales
-----------------------------------------------------------
Onions, leeks, and garlic: Cooking secrets of the 375
Fifty years in Buckingham palace kitchens 15096
Sushi, anyone? 4095

(3 row (s) affected)

E. Use the cast with the like clause
The following example converts the int column (ytd_sales column) to the char (20) column to use the like clause.

Use pubs
Go
Select title, ytd_sales
From titles
Where cast (ytd_sales as char (20) Like '20140901'
And type = 'trad _ cook'
Go

The following is the result set:

Title ytd_sales
-----------------------------------------------------------------------
Fifty years in Buckingham palace kitchens 15096

(1 row (s) affected)