1、CAST和CONVERT关键字
https://blog.csdn.net/lengxuan001/article/details/120422416
2、时间格式
https://www.w3school.com.cn/sql/func_convert.asp
日期+1:
DATEADD(day,1,GETDATE())
日期-1:
DATEADD(day,-1,GETDATE())
3、行转列并用逗号隔开
with t0 AS (SELECT M.SHORTNAME SHORTNAME1,M2.SHORTNAME SHORTNAME2 FROM BOMM BM, BOMD BD, MATERIAL M, MATERIAL M2 WHERE BD.BOMM_ID = BM.ID AND M.ID = BD.MATERIAL_ID AND M2.ID = BM.MATERIAL_ID AND BM.BASIC_BOMTYPE_ID = '7002' AND BM.MATERIAL_ID = '{0}' ) SELECT distinct T0.SHORTNAME2 SHORTNAME,STUFF((SELECT ','+T0.SHORTNAME1 FROM T0 FOR --将T0.SHORTNAME1字段的值转换为xml格式,并用','号隔开,STUFF()函数将合并后的字符串的第一个','号删除
XML PATH('') ), 1, 1, '') AS MATERIALNAME FROM T0
结果:
SHORTNAME MATERIALNAME
(OEM)44克棒冰混合胶粉底料 柠檬酸钠,山梨酸钾(FF-01),羧甲基纤维素钠(ZC-05),瓜尔胶(ZC-06),结冷胶(ZC-11J),黄原胶(ZC-14A)