切换导航
{{systemName}}
{{ info.Title }}
{{info.Title}}
{{ menu.Title }}
{{menu.Title}}
登录
|
退出
搜索
sqlserver常用查询
作者:ych
#### 1.(数据库使用中能不用事务尽量不用事务,事务会锁表,操作多张表可以用事务) 查询拼接(+) ``` select stuff((select ','+title from tb for xml path('')),1,1,'') ``` 查询拼接 ``` SELECT GroupID, STUFF((SELECT ',' + CAST(T3.RealName AS VARCHAR(50)) FROM GroupUser AS T2 LEFT JOIN Base_User T3 ON T3.UserId=T2.UserId WHERE T2.GroupID = T1.GroupID FOR XML PATH('')), 1, 1, '') AS Users FROM GroupUser AS T1 GROUP BY GroupID ``` #### 2.层层嵌套查询(要对查到的结果重命名) ``` select jh from(select jh from (select JH FROM DBO.CHERY)a)b ``` #### 3.查询函数被调用 ``` select o.name,o.crdate,o.refdate,s.* from sys.all_sql_modules s left join sysobjects o on o.id=s.object_id where definition like '%这里填函数名称%' --将ORACLE替换成你要查找的内容 ,P 指 存储select name select name from sysobjects o, syscomments s where o.id = s.id and text like '%ORACLE%' and o.xtype = 'P' ``` #### 4.类型转换 ``` CAST (expression AS data_type) ``` (1)两个表达式的数据类型完全相同。 (2)两个表达式可隐性转换。 (3)必须显式转换数据类型。 #### 5.日期变更 DATEADD() 函数在日期中添加或减去指定的时间间隔。 ``` set @aaa=DATEADD(day,-1,GETDATE())----前一天时间 ``` CONVERT() 函数是把日期转换为新数据类型的通用函数。 CONVERT() 函数可以用不同的格式显示日期/时间数据。 CONVERT(data_type(length),data_to_be_converted,style) data_type(length) 规定目标数据类型(带有可选的长度)。data_to_be_converted 含有需要转换的值。style 规定日期/时间的输出格式。其中style用法: aaa| bbb -|- 111|yy/mm/dd 114|`hh:mi:ss:mmm(24h)` 120 或者 20|`yyyy-mm-dd hh:mi:ss(24h)` 121 或者 21|`yyyy-mm-dd hh:mi:ss.mmm(24h)` 126|`yyyy-mm-ddThh:mm:ss.mmm(没有空格)` #### 6.获取两个日期间的时间 DATEDIFF() 函数返回两个日期之间的相差时间。 ``` DATEDIFF(DAY,@开始时间,@结束时间) ``` #### 7.为null则返回0 ``` ISNULL(UnitsOnOrder,0) ``` #### 8.实现时间截取 ``` LEFT(StatDate,7)-----截取前7位,RIGHT为后。 ``` #### 9.自定义 ``` DECLARE @SDate DATE ``` #### 10.时间比 ``` CONVERT(date, left(s.Atime,10))= '2018-11-27' DATEDIFF(DAY,Atime,'2018-11-28 0:00:00')=0 ``` #### 11.sql中判断的写法是 ``` if @id>0 and @id<40 或者 if @id>0 or @id<40 DECLARE @publishers VARCHAR(50);2 SELECT * FROM dbo.Book WHERE Publishers=ISNULL(@publishers,Publishers) DECLARE @update DATETIME SELECT * FROM A WHERE @updata=0 OR CreateTime>@update ----当传入参数=0是,就是全部 ``` #### 12.写函数与调用 (1)创建 ``` USE [FT] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:
-- Create date: <> -- Description:<计算率> -- ============================================= ALTER FUNCTION [dbo].[f_FloatRate] ( @MpriceDECIMAL(10,4),--价 1 @ZpriceDECIMAL(10,4),--价2 @DrateDECIMAL(10,4)=1.0--率3 ) RETURNS DECIMAL(10,4) AS BEGIN DECLARE @R DECIMAL(10,4) --执行价折前价 Declare @Dprice DECIMAL(10,4) Set @Dprice=@Zprice/@Drate --Set @R = (@Mprice-@Zprice/@Drate)/@Zprice If(@Mprice>@Dprice) Set @R = (@Mprice-@Dprice)/@Dprice Else Set @R = (@Mprice-@Zprice)/@Zprice RETURN @R End ``` (2)调用 ``` Select dbo.f_FloatRate(Price,Price,@X) ``` #### 13,对查询数据处理 ``` case t.[status] when 1 then '已打款' when 0 then '未打款' else '未处理' end as newstatus ``` #### 14,多条件查询,并改变查询结果 ``` select case when v.type=0 and v.TriggleStatus=0 then 'inside' when v.type=0 and v.TriggleStatus=1 then 'outside' when v.type=1 and v.TriggleStatus=1 then 'in' when v.type=1 and v.TriggleStatus=1 then 'out' else 'nostatus' end as'状态' from t_vabyg v ``` #### 15.分页查询 方法一: ``` -- 设置执行时间开始,用来查看性能的 set statistics time on ; -- 分页查询(通用型) select * from (select top pageSize * from (select top (pageIndex*pageSize) * from student order by sNo asc ) -- 其中里面这层,必须指定按照升序排序,省略的话,查询出的结果是错误的。 as temp_sum_student order by sNo desc ) temp_order order by sNo asc -- 分页查询第2页,每页有10条记录 select * from (select top 10 * from (select top 20 * from student order by sNo asc ) -- 其中里面这层,必须指定按照升序排序,省略的话,查询出的结果是错误的。 as temp_sum_student order by sNo desc ) temp_order order by sNo asc; ``` 方法二: ``` set statistics time on; -- 分页查询(通用型) select top pageSize * from student where sNo>= (select max(sNo) from (select top ((pageIndex-1)*pageSize+1) sNo from student order by sNo asc) temp_max_ids) order by sNo; -- 分页查询第2页,每页有10条记录 select top 10 * from student where sNo>= (select max(sNo) from (select top 11 sNo from student order by sNo asc) temp_max_ids) order by sNo; ``` 方法三:利用row_number关键字2005以上 ``` set statistics time on; -- 分页查询(通用型) select top pageSize * from (select row_number() over(order by sno asc) as rownumber,* from student) temp_row where rownumber>((pageIndex-1)*pageSize); set statistics time on; -- 分页查询第2页,每页有10条记录 select top 10 * from (select row_number() over(order by sno asc) as rownumber,* from student) temp_row where rownumber>10; ``` 方法四:offset /fetch next(2012版本及以上才有) ``` set statistics time on; -- 分页查询(通用型) select * from student order by sno offset ((@pageIndex-1)*@pageSize) rows fetch next @pageSize rows only; -- 分页查询第2页,每页有10条记录 select * from student order by sno offset 10 rows fetch next 10 rows only ; ``` 方法五:between and ``` ALTER proc [dbo].[AS_List] @idx int, --查询信息 @pagesize int, @page int, @counts int output AS begin Declare @recdst int,@recdend int Set @recdst=@pagesize*(@page-1)+1 Set @recdend=@recdst+@pagesize-1 if @idx=0 begin select ROW_NUMBER() over(order by addtime desc) rowid, * into #temp from User with(nolock) select * from #temp where rowid between @recdst and @recdend select @counts=COUNT(1) from #temp drop table #temp end end ``` ``` Declare @counts int; exec [dbo].[AS_List] 0,5,2,@counts OUTPUT; select @counts 总数; ``` #### 16.查询多个id拼接起来的数据如(1,2,3,6) ``` ---过程:exec f_getapp_mymenuinfo '1,2,3,6' ---创建过程 Create PROC [dbo].[f_getapp_mymenuinfo] @firstid VARCHAR(20)--一级分类id AS BEGIN begin select name,sortid from [Menu](nolock) where [state]=1 --and PATINDEX('%,' + RTRIM(id) + ',%',',' + @firstid + ',')>0 and CHARINDEX(convert(varchar,id),@firstid)>0 end END ``` #### 17.随机抽取一条记录 利用 SQL Server 的 NewID() 方法就可以很轻松地达到这个目的。 我们要想随机取出 10 条数据,只要加上 TOP 10 就行了: ``` select top 10 *, NewID() as random from table order by random ``` #### 18.一行转多行 ``` create table #t(id int,vc varchar(200)) insert #t select 1 ,'S98001,S95463,S27356' insert #t select 2 ,'T19883,S56765,W34548' ``` 方法一(超耗内存): ``` select row_number() over(order by (select 1)) as id ,c.value('.','varchar(50)') as list from #t a cross apply(select cast('
'+replace(vc,',','
')+'
' as xml) as xmlcode) C1 cross apply xmlcode.nodes('*') t(c) id list -------------------- -------------------------------------------------- S98001 S95463 S27356 T19883 S56765 W34548 (6 行受影响) ``` 方法二: ``` SELECT fs.a FROM #t t CROSS APPLY dbo.f_split(t.vc,',') fs dbo.f_split为表值函数 --实现split功能 的函数 ALTER function [dbo].[f_split](@SourceSql varchar(8000),@StrSeprate varchar(10)) returns @temp table(a varchar(100)) as begin declare @i int set @SourceSql=rtrim(ltrim(@SourceSql)) set @i=charindex(@StrSeprate,@SourceSql) while @i>=1 begin insert @temp values(left(@SourceSql,@i-1)) set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(@StrSeprate,@SourceSql) end if @SourceSql<>'' insert @temp values(@SourceSql) return end ``` #### 19.创建临时表 ``` ----定义表变量存储 declare @RoomSoner table ( [MyIdx] varchar(50) NULL , [Type] int NULL , [State] int NULL , [Location] int NULL ) ``` #### 20.存储过程查询过滤id及null ``` where (Id=@Id or @Id=0 or @Id='' or @Id=null ) AND Type=1 AND (@startTime=''or @startTime=null or A.create_time>=@startTime) and (@endTime=''or @endTime=null or A.create_time<=@endTime) ``` #### 21.查询根据时间查询数据 ``` --查询当天: select * from info where DateDiff(dd,datetime,getdate())=0 --查询24小时内的: select * from info where DateDiff(hh,datetime,getDate())<=24 --本月记录: SELECT * FROM 表 WHERE datediff(month,[dateadd],getdate())=0 - --本周记录: SELECT * FROM 表 WHERE datediff(week,[dateadd],getdate())=0 --查询上月 SELECT * FROM 表 where DATEDIFF(month,inputdate,GETDATE())=1 ``` #### 22.分割查询并批量插入 ``` insert into T_person(Age) SELECT 1 RoleId from dbo.f_split('1,2,4',',') fs; ``` #### 23.插入并查询id ``` insert into Role(role_name,create_time,is_del) values(@role_name,GETDATE(),@is_del); select @id=@@identity ``` #### 24.事物 ``` ----参考:https://blog.csdn.net/zhenglianghui163/article/details/79013798 begin try --开启异常捕获 begin tran --开启事务 commit tran --提交事务 return 1 end try begin catch --异常捕获执行 rollback tran --回滚事务 return 0 end catch ``` #### 25.添加列l修改列类型删除列 ``` USE s_DB GO alter table Product add data_state int not null default 0 ---修改列 USE s_DB GO ALTER TABLE Bs_Detail ALTER COLUMN flag_pay int --修改自增列 USE s_DB GO --删除字段ID alter table Bs_Record drop column [id] ---增加ID自动增长字段 alter table Bs_Record add [id] int identity(1,1) ``` #### 26.查询并将null转为0 ``` select isnull(a,0) from table ``` #### 27.存储过程链接查询 ``` declare @sql2 varchar(max); create table #member_own ( owncash varchar(max) ) set @sql2 = 'SELECT owncash FROM OPENQUERY(Link1143, '+'''exec vvvve.dbo.xc_select_own '+' ' +cast(@Idx as varchar(20))+''')' INSERT INTO #member_own exec(@sql2) ``` #### 28.快速备份表 原表存在的话这样用: ``` insert into a select * from b ``` 原表不存在的话这样用: ``` select * into a from b ``` #### 29.多行转一行(T_index_Banner表以title字段区分并进行拼接) ``` SELECT DISTINCT title, STUFF((SELECT ',' + link FROM T_index_Banner WHERE title = T.title FOR XML PATH('')), 1, 1, '') AS link FROM T_index_Banner AS T --将有逗号的数据转换为一个表存储id加数据 GO DECLARE @Text VARCHAR(200) SET @Text = 'test1,test2' DECLARE @xml XML SET @xml = CONVERT(XML,''+ REPLACE(@Text,',','')+'') CREATE TABLE #TMP1 ( iID VARCHAR(50), sText VARCHAR(200) ) INSERT INTO #TMP1 SELECT NEWID(),b.value FROM (SELECT @xml AS 'XML') a CROSS APPLY (SELECT Tb.a.value('.','VARCHAR(200)') as value FROM a.xml.nodes('/a') AS Tb(a)) b SELECT * FROM #TMP1 GO --将一个Table中某一列的数据拼成逗号分隔的形式 GO CREATE TABLE #TMP2 ( iID INT, sText VARCHAR(200) ) INSERT INTO #TMP2( iID, sText ) VALUES ( 1, 'test1'); INSERT INTO #TMP2( iID, sText ) VALUES ( 1, 'test2'); DECLARE @TMP2RES VARCHAR(200) SET @TMP2RES = (SELECT sText+',' FROM #TMP2 FOR XML PATH('')) SET @TMP2RES = SUBSTRING(@TMP2RES,0,LEN(@TMP2RES)-1) SELECT @TMP2RES GO ``` (2)用leftjoin以日期联表进行数据筛选逗号分割 ``` go create table TableA (AID varchar(2),strBID varchar(9)) insert into TableA select 'A1',null union all select 'A2',',B1,B3,' union all select 'A3',',B1,B2,' union all select 'A4',',B1,B4,' go create table TableB (BID varchar(2),UpdateTime datetime) insert into TableB select 'B1','20120130' union all select 'B2','20120131' union all select 'B3','20120201' union all select 'B4','20120202' go create function aa(@num int) returns varchar(100) as begin declare @sql varchar(100) set @sql='' --设置当前日期,正常getdate()即可 declare @t datetime set @t='20120203' select @sql=@sql+AID+',' from TableA a left join TableB b on charindex(','+BID+',',strBID)>0 where b.UpdateTime between dateadd(d,-@num,@t) and @t select @sql=left(@sql,len(@sql)-1) return @sql end select dbo.aa(2) /* A2,A4 */ ``` (3)简单查询拼接不需要分组 ``` SELECT a into #T from dbo.f_split('1,2,4',',') fs; select * from #T; declare @sql varchar(100) set @sql='' select @sql=@sql+a.a+',' from #T a select @sql=left(@sql,len(@sql)-1) select * from #T where charindex(','+ltrim(a)+',',','+@sql+',')>0 ----定义表变量存储 declare @RoomSoner table ( [MyIdx] varchar(50) NULL , [Type] int NULL default(0), [State] int NULL default(0), [Location] int NULL default(0) ) insert into @RoomSoner(MyIdx) select a from #t; select * from @RoomSoner drop table #T; ``` #### 30.查询存储过程调用情况 ``` SELECT database_id,* FROM sys.databases WHERE database_id> 4 SELECT a.name AS 存储过程名称, a.create_date AS 创建日期, a.modify_date AS 修改日期, b.last_execution_time AS 最后执行日期, b.execution_count AS 执行次数 FROM sys.procedures a LEFT JOIN sys.dm_exec_procedure_stats b ON a.object_id = b.object_id AND b.database_id ='5' WHERE a.is_ms_shipped =0 --去掉系统存储过程 ORDER BY b.database_id ``` #### 31.关联更新 ``` update Article set Article.CreateUserId = TaskUser.id from Article,TaskUser where Article.UserName = TaskUser.account; ``` #### 32.创建表 ``` CREATE TABLE [dbo].[AppDownStaticDetail]( [id] [bigint] IDENTITY(1,1) primary key NOT NULL, [Ip] [nvarchar](300) NOT NULL, [Type] [int] Not NULL, [AddTime] datetime NOT NULL ) ON [PRIMARY] ``` #### 33.使用CTE进行多次查询,CTE是可以在跟随其后的查询中多次引用的 ``` with tmp(id) as ( select id from users where name like '航%'; ) select * from tmp; select * from orders where userId in (select id from tmp); ``` #### 34.分割id到一行 ``` select @menuIds = @menuIds + cast(MenuId as varchar(255)) +',' from AdminRole_Menu (nolock) where RoleId=@roleId set @menuIds=left(@menuIds,len(@menuIds) - 1) ``` #### 35.分割的id拼接 首先编写通用分割函数 ``` GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER function [dbo].[tableListSplit](@SourceSql varchar(8000),@StrSeprate varchar(10)) returns @temp table(a varchar(100)) as begin declare @i int set @SourceSql=rtrim(ltrim(@SourceSql)) set @i=charindex(@StrSeprate,@SourceSql) while @i>=1 begin insert @temp values(left(@SourceSql,@i-1)) set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(@StrSeprate,@SourceSql) end if @SourceSql<>'' insert @temp values(@SourceSql) return end ``` 在存储过程调用 ``` DECLARE @fillid TABLE( id INT ) INSERT INTO @fillid( id )SELECT a AS id FROM dbo.tableListSplit('1,2,3',',') update Log SET states =1 WHERE id IN ( SELECT id FROM @fillid) ``` #### 36.发送邮件 ``` exec msdb..sp_send_dbmail @profile_name ='DBMAIL',--profile名称 @recipients = 'chengsejuntuan@qq.com;niao@qq.com;',--收件人邮箱(有多个邮箱用分号分开) @subject = @subject,--邮件标题 @body = @Content--邮件内容 ``` #### 37.今周和上一周时间处理 ``` declare @startime datetime declare @endtime datetime if(datename(dw,GETDATE())='星期日') begin set @startime = DATEADD(wk, DATEDIFF(wk,0,getdate()), -7) set @endtime = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) end else begin set @startime = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) set @endtime = DATEADD(wk, DATEDIFF(wk,0,getdate()), 7) end select @startime,@endtime ``` #### 38.周统计处理 ``` --按周统计 总注册量,android,ios,h5 SELECT TOP 10 DATENAME(year,RegisterDate) 年, DATENAME(week,RegisterDate) 周, COUNT(1) 总注册量, SUM(case RegDeviceType when 0 then 1 else 0 end) 未知设备, SUM(case RegDeviceType when 2 then 1 else 0 end) 安卓, SUM(case RegDeviceType when 5 then 1 else 0 end) H5, SUM(case RegDeviceType when 4 then 1 else 0 end) IOS, SUM(case LEFT(ACCOUNTS,2) when 'GG' then 1 else 0 end) 谷歌注册, SUM(case LEFT(ACCOUNTS,2) when 'FB' then 1 else 0 end) 脸书注册, SUM(case LEFT(ACCOUNTS,2) when 'AP' then 1 else 0 end) 苹果注册, SUM(case LEFT(ACCOUNTS,2) when 'AP' then 0 when 'GG' then 0 when 'FB' then 0 else 1 end) 手机注册 FROM [Account] t(NOLOCK) WHERE [RegisterDate] BETWEEN '2019-1-1' AND '2021-1-1' GROUP BY DATENAME(year,RegisterDate),DATENAME(week,RegisterDate) ORDER BY CONVERT(INT,DATENAME(week,RegisterDate)); ``` #### 39.创建表 ``` CREATE TABLE TestTable ( Id int IDENTITY(1,1) PRIMARY KEY, Name varchar(50) ) GO EXEC sp_addextendedproperty 'MS_Description', N'主键id', 'SCHEMA', N'dbo', 'TABLE', N'TestTable', 'COLUMN', N'Id' GO EXEC sp_addextendedproperty 'MS_Description', N'名称', 'SCHEMA', N'dbo', 'TABLE', N'TestTable', 'COLUMN', N'Name' GO ``` #### 40.删除表 ``` IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type IN ('U')) DROP TABLE [dbo].[TestTable] GO ``` #### 41.获取所有表 ``` DECLARE @TableInfo TABLE ( name VARCHAR(50) , sumrows VARCHAR(11) , reserved VARCHAR(50) , data VARCHAR(50) , index_size VARCHAR(50) , unused VARCHAR(50) , pk VARCHAR(50) ) DECLARE @TableName TABLE ( name VARCHAR(50) ) DECLARE @name VARCHAR(50) DECLARE @pk VARCHAR(50) INSERT INTO @TableName ( name ) SELECT o.name FROM sysobjects o , sysindexes i WHERE o.id = i.id AND o.Xtype = 'U' AND i.indid < 2 ORDER BY i.rows DESC , o.name WHILE EXISTS ( SELECT 1 FROM @TableName ) BEGIN SELECT TOP 1 @name = name FROM @TableName DELETE @TableName WHERE name = @name DECLARE @objectid INT SET @objectid = OBJECT_ID(@name) SELECT @pk = COL_NAME(@objectid, colid) FROM sysobjects AS o INNER JOIN sysindexes AS i ON i.name = o.name INNER JOIN sysindexkeys AS k ON k.indid = i.indid WHERE o.xtype = 'PK' AND parent_obj = @objectid AND k.id = @objectid INSERT INTO @TableInfo ( name , sumrows , reserved , data , index_size , unused ) EXEC sys.sp_spaceused @name UPDATE @TableInfo SET pk = @pk WHERE name = @name END SELECT F.name , F.reserved , F.data , F.index_size , RTRIM(F.sumrows) AS sumrows , F.unused , f.name AS tdescription, F.pk FROM @TableInfo F LEFT JOIN ( SELECT name = CASE WHEN A.COLORDER = 1 THEN D.NAME ELSE '' END , tdescription = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, '') ELSE '' END FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE INNER JOIN SYSOBJECTS D ON A.ID = D.ID AND D.XTYPE = 'U' AND D.NAME <> 'DTPROPERTIES' LEFT JOIN sys.extended_properties F ON D.ID = F.major_id WHERE a.COLORDER = 1 AND F.minor_id = 0 ) P ON F.name = p.name; ``` #### 42.获取表结构 ``` SELECT [number]=a.colorder, [column] =a.name, [datatype]=b.name, [length]=COLUMNPROPERTY(a.id,a.name,'PRECISION'), [identity]=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, [key]=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) then '√' else '' end, [isnullable]=case when a.isnullable=1 then '√'else '' end, [default]=isnull(e.text,''), [remark]=isnull(g.[value],a.name) FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0 where d.name='TestTable' order by a.id,a.colorder; ``` #### 43.获取主键字段 ``` SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='TestTable' ; ``` #### 44.left join 过滤空匹配 ``` --- 条件过滤 select t1.*,t2.Name,t2.Ip from Table_1 t1 left join Table_2 t2 on t1.Name=t2.Name and t2.Name!=' '; --- DISTINCT select DISTINCT(t1.Id),t1.Name,t1.Ip,t2.Name from Table_1 t1 left join Table_2 t2 on t1.Name=t2.Name; ``` #### 45.状态对调 ``` UPDATE Push_Record SET IsRead = CASE IsRead WHEN 0 THEN 1 WHEN 1 THEN 0 END; ``` #### 46.备份脚本 backup.sql ``` GO DECLARE @backupTime VARCHAR(20) DECLARE @fileName VARCHAR(1000) SELECT @backupTime=(CONVERT(VARCHAR(8), GETDATE(), 112) +REPLACE(CONVERT(VARCHAR(5), GETDATE(), 114), ':', '')) SELECT @fileName='C:\code\sql\DB_'+@backupTime+'.bak' backup database WLYYWX to disk=@fileName ``` WLYYWX备份.bat ``` sqlcmd -S . -i C:\code\sql\backup.sql ``` #### 47.使用CONVERT函数将字符串转换为日期时间格式 拼接得到2023-11-01 08:32 ``` DECLARE @date DATETIME = '2023-11-01 00:00:00.000'; DECLARE @time VARCHAR(10) = '08:26-08:32'; SELECT CONVERT(VARCHAR(19), DATEADD(MINUTE, DATEDIFF(MINUTE, '00:00', SUBSTRING(@time, CHARINDEX('-', @time) + 1, 5)), @date), 120) AS result; ``` 拼接得到2023-11-01 08:26 ``` DECLARE @date DATETIME = '2023-11-01 00:00:00.000'; DECLARE @time VARCHAR(10) = '08:26-08:32'; SELECT CONVERT(VARCHAR(19), DATEADD(MINUTE, DATEDIFF(MINUTE, '00:00', SUBSTRING(@time, 1, 5)), @date), 120) AS result; ```
评论区
先去登录
版权所有:机遇屋在线 Copyright © 2021-2025 jiyuwu Co., Ltd.
鲁ICP备16042261号-1