Bing Yee程序人生

关注互联网产品技术学习,应用开发~

CAST CONVERT转换日期和时间数据类型

2020-7-8 9:41:06 类别:数据库
摘要:MSSQL 不同类型的数据连接符操作示例
USE AdventureWorks2008R2;
GO
--CAST

Select 'CAST' "CAST",
CAST('1990-11-1' AS VARCHAR(10)) 转为字符串,
CAST('1990-11-1' AS DATETIME) 转为日期

--CONVERT
Select 'CONVERT' "CONVERT",
CONVERT(VARCHAR(10),'2999') 转为字符串,
CONVERT(INT,'2999') 转为整数

--同时使用 CAST 和 CONVERT

-- Use CAST
Select SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
Where CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT.
Select SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
Where CONVERT(int, ListPrice) LIKE '3%';
GO

--B. 将 CAST 与算术运算符结合使用

Select CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS Computed
FROM Sales.SalesPerson
Where CommissionPCT != 0;
GO
/*
C. 使用 CAST 进行连接
以下示例使用 CAST 连接非字符型非二进制表达式。
*/
Select 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
Where ListPrice BETWEEN 350.00 AND 400.00;
GO

/*
D. 使用 CAST 生成可读性更高的文本
以下示例使用选择列表中的 CAST 将 Name 列转换为 char(10) 列。
*/

Select DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice
FROM Sales.SalesOrderDetail AS s
JOIN Production.Product AS p
    ON s.ProductID = p.ProductID
Where Name LIKE 'Long-Sleeve Logo Jersey, M';
GO

Select DISTINCT Name, s.UnitPrice
FROM Sales.SalesOrderDetail AS s
JOIN Production.Product AS p
    ON s.ProductID = p.ProductID
Where Name LIKE 'Long-Sleeve Logo Jersey, M';
GO

/*
E. 将 CAST 与 LIKE 子句一起作用
以下示例将 money 列 SalesYTD 转换为 int 列,然后再转换为 char(20) 列,以便在 LIKE 子句中使用该列。
*/
Select p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityID
FROM Person.Person AS p
JOIN Sales.SalesPerson AS s
    ON p.BusinessEntityID = s.BusinessEntityID
Where CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO

/*
G. 对 datetime 数据使用 CAST 和 CONVERT
以下示例显示当前日期和时间,使用 CAST 将当前日期和时间更改为字符数据类型,
然后使用 CONVERT 以 ISO 8901 格式显示日期和时间。
*/
Select
   GETDATE() AS UnconvertedDateTime,
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;
GO

/*
以下示例与上述示例几乎完全相反。 该示例将日期和时间显示为字符数据,使用 CAST 将字符数据更改为 datetime 数据类型,然后使用 CONVERT 将字符数据更改为 datetime 数据类型。
*/

Select
   '2006-04-25T15:50:59.997' AS UnconvertedText,
   CAST('2006-04-25T15:50:59.997' AS datetime) AS UsingCast,
   CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO

/*
H. 使用 CONVERT 处理二进制和字符数据
以下示例使用不同样式显示转换二进制和字符数据的结果。
*/

--Convert the binary value 0x4E616d65 to a character value.
Select CONVERT(char(8), 0x4E616d65, 0) AS [Style 0, binary to character];

--The following example shows that Style 2 does not truncate the
--result because the characters 0x are not included in  the result.
Select CONVERT(char(8), 0x4E616d65, 2) AS [Style 2, binary to character];

--Convert the character value 'Name' to a binary value.
Select CONVERT(binary(8), 'JackChen', 0) AS [Style 0, character to binary];

Select CONVERT(binary(4), '0x4E616D65', 1) AS [Style 1, character to binary];

/*
I. 转换日期和时间数据类型
以下示例演示了日期、时间及 datetime 数据类型的转换。
*/

DECLARE @d1 date, @t1 time, @dt1 datetime;
SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
select @d1 date格式,@t1 time格式,@dt1 datetim格式
-- When converting date to datetime the minutes portion becomes zero.
Select @d1 AS [date], CAST (@d1 AS datetime) AS [date as datetime];
-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
Select @t1 AS [time], CAST (@t1 AS datetime) AS [time as datetime];
-- When converting datetime to date or time non-applicable portion is dropped.
Select @dt1 AS [datetime], CAST (@dt1 AS date) AS [datetime as date], CAST (@dt1 AS time) AS [datetime as time];
710次阅读  标签:MSSQL,CAST,CONVERT
上一篇:bootstrap 表格中 下拉菜单滚动条问题

共27条评论

measoms(1楼)-2021-4-28 15:51:24
审核中

measoms(2楼)-2021-4-28 16:42:15
审核中

measoms(3楼)-2021-4-29 2:14:56
审核中

Intonge(4楼)-2021-5-9 8:14:09
审核中

twixecy(5楼)-2021-5-9 19:43:35
审核中

twixecy(6楼)-2021-5-10 17:27:17
审核中

twixecy(7楼)-2021-5-12 13:52:05
审核中

twixecy(8楼)-2021-5-12 19:59:27
审核中

sheajew(9楼)-2021-5-16 13:32:39
审核中

sheajew(10楼)-2021-5-17 4:24:42
审核中

sheajew(11楼)-2021-5-17 9:13:55
审核中

sheajew(12楼)-2021-5-17 14:05:52
审核中

Pydrase(13楼)-2021-5-22 1:46:32
审核中

Pydrase(14楼)-2021-5-22 14:04:37
审核中

Pydrase(15楼)-2021-6-8 16:39:33
审核中

Pydrase(16楼)-2021-6-9 0:13:04
审核中

Pydrase(17楼)-2021-6-9 4:45:50
审核中

Pydrase(18楼)-2021-6-23 0:55:10
审核中

Pydrase(19楼)-2021-6-23 7:57:52
审核中

Pydrase(20楼)-2021-7-9 17:36:58
审核中

Pydrase(21楼)-2021-7-10 2:55:03
审核中

Pydrase(22楼)-2021-7-26 16:51:33
审核中

Pydrase(23楼)-2021-7-27 2:27:28
审核中

Pydrase(24楼)-2021-7-27 8:16:43
审核中

Pydrase(25楼)-2021-8-19 23:28:25
审核中

Pydrase(26楼)-2021-8-20 8:47:51
审核中

Pydrase(27楼)-2021-8-20 17:45:04
审核中

发表评论

文章分类


友情链接