时间相关
-
取今天日期:
SELECT GETDATE()
Output: 2022-11-04 10:24:07.130 (时间格式)
-
月份增加:
SELECT DATEADD(mm,3,'2020-12-30')
Output: 2021-03-30 00:00:00.000 (时间格式)
-
只显示月份:
SELECT month('2020-12-30')
Output: 12
-
拿字符串更改日期格式
select STUFF ('20221010', 5, 0, '/')
STUFF(你想做修改的字符串,修改的开始位置, 删几个位置的字符,新添加的字符串)
Output: 2022/1010多重修改,输出为 yyyy/mm/10
select STUFF(STUFF ('20221010', 5, 0, '/'),8,0,'/')
Output: 2022/10/10
-
拿字符串更改日期格式2
select STUFF(STUFF('2020/01/01', 5, 1, ''),7,1,'')
Output: 20200101
字符串相关
- 长字符串里分离特定字符
STRING_SPLIT (string, separator)select *, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as row_num FROM ( select value as CODE from STRING_SPLIT('123-23-321','-') )t
结果
- 加行号不改变原来表顺序
select ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as row_num
FROM t
- 根据a里不同值,对b列排序
RANK () OVER (
PARTITION BY a
ORDER BY b DESC
) rank_no
多列拼接后转成同一行
select
类别,
名称 = (
stuff(
(select ',' + 名称 from Table_A where 类别 = A.类别 for xml path('')),
1,
1,
''
)
)
from Table_A as A group by 类别
动态行转列
DECLARE @PivotColHeader VARCHAR(MAX)
SELECT @PivotColHeader = --使用COALESCE函数生成列标题
COALESCE(
@PivotColHeader + ',[' + cast(仓库 as varchar) + ']',
'[' + cast(仓库 as varchar) + ']'
)
FROM( select 某列 from 某表 )T
PRINT @PivotColHeader
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N
'
select *
INTO #TEMP1
from
(
select
,仓库
,WH_QTY --库存数
from 某表
where ELT_TIME >= getdate()-1
)t
PIVOT(
sum(WH_QTY)
for 仓库in ( ' + @PivotColHeader + ' )
) as t2
SELECT
CONVERT(nvarchar,ETL_TIME,20) AS ETL_TIME --拉取数据时间
,' + @PivotColHeader + '
FROM T1
LEFT JOIN #TEMP1 t_wh
ON T_WH.MATNR = T1.MATNR
'
EXECUTE sp_executesql @PivotTableSQL
十六进制转字符串
SELECT sys.fn_varbintohexstr(PMAT_GUID)