标注为
红色的是个人觉得比较有用的几个函数。
序号 | 名称 | 类型 | 说明 | MSDN |
1 | Concat | 字符串类 | 返回作为串联两个或更多字符串值的结果的字符串。 | msdn |
2 | Format | 字符串类 | 返回以指定的格式和可选的区域性格式化的值。 | msdn |
3 | CHOOSE | 逻辑类 | 在 SQL Server 中从值列表返回指定索引处的项。 | msdn |
4 | IIF | 逻辑类 | 在 SQL Server 中,根据布尔表达式计算为 true 还是 false,返回其中一个值。 | msdn |
5 | DATEFROMPARTS | 日期类 | 返回日期指定的年、 月和日的值。 | msdn |
6 | DATETIMEFROMPARTS | 日期类 | 返回datetime值的指定的日期和时间。 | msdn |
7 | DATETIME2FROMPARTS | 日期类 | 返回datetime2值的指定的日期和时间并使用指定的精度。 | msdn |
8 | DATETIMEOFFSETFROMPARTS | 日期类 | 返回datetimeoffset值的指定的日期和时间并使用指定的偏移量和精度。 | msdn |
9 | TIMEFROMPARTS | 日期类 | 返回时间值指定的时间并使用指定的精度。 | msdn |
10 | EOMONTH | 日期类 | 返回包含指定日期的月份的最后一天(具有可选偏移量)。 | msdn |
11 | PARSE | 转换类 | 返回 SQL Server 中转换为所请求的数据类型的表达式的结果。 | msdn |
12 | TRY_PARSE | 转换类 | 在 SQL Server 中,返回表达式的结果(已转换为请求的数据类型); 如果强制转换失败,则返回 Null。 TRY_PARSE 仅用于从字符串转换为日期/时间和数字类型。 | msdn |
13 | TRY_CONVERT | 转换类 | 返回转换为指定数据类型的值(如果转换成功);否则返回 Null。 | msdn |
SET NOCOUNT ON
--1. CONCAT
--concat 的好处:1. 可以跨数据类型; 2. 忽略 NULL
SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25', NULL ) AS r1, 'Happy'+NULL AS r2;
/*
r1 r2
------------------------------ ------
Happy Birthday 11/25 NULL
*/
--2. FORMAT
----1. 可以根据区域得到不同的日期格式
----2. 可以自定义格式字符串(日期和数字)
----3. 可以根据区域得到不同的数据格式(如加 $ )
DECLARE @d DATETIME = '10/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result';
/*
US English Result Great Britain English Result German Result Simplified Chinese (PRC) Result
10/1/2011 01/10/2011 01.10.2011 2011/10/1
*/
SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'
,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'
,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'
,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';
/*
US English Result Great Britain English Result German Result Chinese (Simplified PRC) Result
Saturday, October 1, 2011 01 October 2011 Samstag, 1. Oktober 2011 2011年10月1日
*/
DECLARE @d2 DATETIME = GETDATE();
SELECT FORMAT( @d2, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result'
,FORMAT(123456789,'###-##-####') AS 'Custom Number Result';
/*
DateTime Result Custom Number Result
15/12/2017 123-45-6789
*/
SELECT FORMAT(cast('07:35' as time), N'hh.mm') AS r1
,FORMAT(cast('07:35' as time), N'hh\.mm') AS r2
,FORMAT(GETDATE(), N'yyyy-MM-dd HH.mm.ss') AS r3
,FORMAT(GETDATE(), N'yyyy-MM-dd HH:mm:ss') AS r4
/*
r1 r2 r3 r4
NULL 07.35 2017-12-15 09.43.16 2017-12-15 09:43:16
*/
--3. CHOOSE
---- 避免了多次的 CASE WHEN
SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS r;
/*
r
Developer
*/
--4. IIF
---- IIF ( boolean_expression, true_value, false_value )
---- 快速写CASE WHEN 的方法,最多也只能10层
DECLARE @a int = 45, @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;
/*
Result
TRUE
*/
--5. DATEFROMPARTS
----DATEFROMPARTS ( year, month, day )
----返回日期指定的年、 月和日的值。 返回类型 date
SELECT DATEFROMPARTS ( 2010, 12, 31 ) AS Result;
/*
Result
2010-12-31
*/
--6. DATETIMEFROMPARTS
----DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
----返回datetime值的指定的日期和时间。
SELECT DATETIMEFROMPARTS ( 2010, 12, 31, 23, 59, 59, 0 ) AS Result;
/*
Result
2010-12-31 23:59:59.000
*/
--7. DATETIME2FROMPARTS
----DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
----返回datetime2值的指定的日期和时间并使用指定的精度。
SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS Result;
/*
Result
2010-12-31 23:59:59
*/
--8. DATETIMEOFFSETFROMPARTS
----DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
----返回datetimeoffset值的指定的日期和时间并使用指定的偏移量和精度。
SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS Result;
/*
Result
2010-12-31 14:23:23.0000000 +12:00
*/
--9. TIMEFROMPARTS
----TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
----返回时间值指定的时间并使用指定的精度。
SELECT TIMEFROMPARTS ( 23, 59, 59, 0, 0 ) AS Result;
/*
Result
23:59:59
*/
--10. EOMONTH
----EOMONTH ( start_date [, month_to_add ] )
----tart_date 日期表达式,指定要为其返回该月的最后一天的日期。
----month_to_add 指定要添加的月数的可选整数表达式start_date。
----返回包含指定日期的月份的最后一天(具有可选偏移量)。 返回类型 date
DECLARE @date DATETIME = '12/1/2011';
SELECT EOMONTH ( @date ) AS Result,EOMONTH ( @date,-3 ) AS Result2;
/*
Result Result2
2011-12-31 2011-09-30
*/
--11. PARSE
----PARSE ( string_value AS data_type [ USING culture ] )
----返回 SQL Server 中转换为所请求的数据类型的表达式的结果。
----
--A. PARSE 为 datetime2
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;
/*
Result
---------------
2010-12-13 00:00:00.0000000
*/
--B. PARSE 与货币符号
SELECT PARSE('€345,98' AS money USING 'de-DE') AS Result;
/*
Result
---------------
345.98
*/
--C. 使用隐式设置的语言进行 PARSE
-- The English language is mapped to en-US specific culture
SET LANGUAGE 'English';
SELECT PARSE('12/16/2010' AS datetime2) AS Result;
/*
Result
2010-12-16 00:00:00.0000000
*/
--12. TRY_PARSE
----TRY_PARSE ( string_value AS data_type [ USING culture ] )
----在 SQL Server 中,返回表达式的结果(已转换为请求的数据类型);如果强制转换失败,则返回 Null。 TRY_PARSE 仅用于从字符串转换为日期/时间和数字类型。
----
--A. TRY_PARSE 的简单示例
SELECT TRY_PARSE('Jabberwokkie' AS datetime2 USING 'en-US') AS Result;
/*
Result
---------------
NULL
*/
--B. 检测 TRY_PARSE 的 Null 值
SELECT
CASE WHEN TRY_PARSE('Aragorn' AS decimal USING 'sr-Latn-CS') IS NULL
THEN 'True'
ELSE 'False'
END
AS Result;
/*
Result
---------------
True
*/
--C. 将 IIF 用于 TRY_PARSE 和隐式区域性设置
SET LANGUAGE English;
SELECT IIF(TRY_PARSE('01/01/2011' AS datetime2) IS NULL, 'True', 'False') AS Result,TRY_PARSE('01/01/2011' AS datetime2) AS r2;
/*
Result r2
False 2011-01-01 00:00:00.0000000
*/
--13. TRY_CONVERT
----TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
----返回转换为指定数据类型的值(如果转换成功);否则返回 Null。
--A. TRY_CONVERT 返回 null
SELECT
CASE WHEN TRY_CONVERT(float, 'test') IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
/*
复制
Result
------------
Cast failed
*/
--以下示例演示表达式必须采用所需的格式。
SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO
/*
Result
----------------------
NULL
*/
--B. TRY_CONVERT 将失败,并出现错误
--下面的示例演示明确不允许转换时 TRY_CONVERT 返回错误。
SELECT TRY_CONVERT(xml, 4) AS Result;
/*
--此语句的结果是一个错误,因为整数无法转换为 xml 数据类型。
Explicit conversion from data type int to xml is not allowed.
*/
--C. TRY_CONVERT 成功
--此示例演示表达式必须采用所需的格式。
SET DATEFORMAT mdy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
/*
Result
----------------------------------
2010-12-31 00:00:00.0000000
*/