在SQL中转换datetime [英] Convert datetime in a SQL
问题描述
大家好，
我使用以下查询列出我的数据库，该数据库在过去2小时内没有日志备份。我的输出中的'T'是尊重日期和时间。我正在尝试许多不同的方式来转换它，但没有运气。任何人都可以帮助我在更好的输出中帮助我在日期和时间之间没有'T'。
声明 @ xml nvarchar （max）
声明 @ datetime datetime
声明 @ now nvarchar （max）
声明 @ body nvarchar （max）
set language ENGLISH
- set @ now = DATEADD（hour，-2，getdate（））
- select * from sys.databases where re covery_model_desc ='FULL'
如果 存在（ select ' x' from sysobjects 其中 name = ' temp_tbl'）
开始
drop table temp_tbl
end
select database_name， type ，max（backup_start_date）backupdate
into temp_tbl 来自 msdb.dbo.backupset
其中 database_name in
（选择名称来自 sys.databases 其中 recovery_model_desc = ' FULL'）
group by database_name， type
order by database_name，类型
如果 存在（选择 ' x' 来自 sysobjects 其中 name = ' final_table'）
开始
drop table final_table
end
create table final_table（DBName nvarchar （ 500 ），FullBackup_date datetime ，LogBackupDate datetime ）
insert into final_table（DBName）
selecT distinct Database_name From temp_tbl
更新 x
设置 x.FullBackup_date = y.backupdate
来自 final_table x，temp_tbl y
其中 x.DBname = y.database_name
和 y.type = ' D'
update x
set x.LogBackupDate = y.backupdate
from final_table x，temp_tbl y
where x.DBname = y.database_name
和 y.type = ' L'
set @ xml =（选择 CAST（（
selecT DBName as ' td'，' '，
FullBackup_date as ' td'，' '，
LogBackupDate a s ' td'
FROM final_table 其中 LogBackupDate< （ convert （ varchar （ 30 ）， （dateadd（hour，-2，getdate（）））， 121 ））
for xml路径（' tr'），elements） as nvarchar （max））
）
SET @ body =
' < H3>数据库最近2小时未备份< / h3>
'
开始
SET @ body = @ body + @ xml + ' < table border =1>< tbody>< tr> <的第i; DBName< / th>< th> FullBackup_date< / th>< th> LogBackupDate< / th>< / tr>< / tbody>< / table>'
声明 < span class =code-sdkkeyword> @ subject varchar （ 250 ）
选择 @ subject = ' 警告：数据库最近2小时没有日志备份'
EXEC msdb.dbo.sp_send_dbmail
@ profile_name = ' DBA' ，
@ recipients = ' anjum.sumaiyas @ lowes.com'，
- @subject = N'Databases不支持最后2小时'，
@ bod y = @ body ，
@ body_format = ' HTML'，
@ subject = @受试者;
END
set nocount off
< b>我尝试了什么：
i我这样得到输出
DBName FullBackup_date LogBackupDate
DB1 2019-04-01T06：21：25 2019-04-01T06：52：31
DB2 2019-02-18T07：08：47 2019-04- 01T06：52：29
DB3 2019-02-18T07：08：25 2019-04-01T06：52：08
进一步上面的OP评论...尝试替换set @ xml =（ select CAST（（
selecT DBName as ' td'，' '，
FullBackup_dateas ' td'，' '，
LogBackupDate as ' td'
FROM final_table 其中 LogBackupDate< （ convert （ datetime ，（dateadd（hour，-2，getdate（）））， 121 ））
xml路径（'' tr'），elements） as nvarchar （max））
）
set @ xml =（选择 CAST（（
选择 DBName 为 ' td'，' '，
FORMAT （FullBackup_date，' yyyyMMdd HHmmss'） as ' td '，' '，
FORMAT （LogBackupDate，' yyyyMMdd HHmmss'） as ' td'
FROM final_table 其中 LogBackupDate< dateadd（hour，-2，getdate（））
for xml path（' tr'），elements） as nvarchar （max ））
）注意事项 - 你已经声明你使用的是
datetime类型，所以要摆脱a的转换datetime到WHERE语句中的varchar
如果您不希望使用该特定格式的日期，请尝试使用其中一种标准格式 - 请参阅此列表 SQL Server日期格式Cheatsheet | TablePlus [ ^ ]
鉴于这是一个针对人类而非系统的电子邮件，您的用户可能更喜欢格式113之类的内容，例如SELECT CONVERT（VARCHAR（26），GETDATE（），113）显示为
10 Apr 2019 15：21：50：670
Hi All,
I am using below query to list my databases which has no log backup for last 2 hours. I am getting 'T' in my output wih respect date and time. I am trying many different ways to convert it but no luck. Can anyone help me out in the better output which has no 'T' in between date and time.
declare @xml nvarchar(max)
declare @datetime datetime
declare @now nvarchar(max)
declare @body nvarchar(max)
set language ENGLISH
--set @now= DATEADD (hour,-2,getdate())
--select * from sys.databases where recovery_model_desc='FULL'
if exists (select 'x' from sysobjects where name ='temp_tbl')
begin
drop table temp_tbl
end
select database_name,type,max(backup_start_date) backupdate
into temp_tbl from msdb.dbo.backupset
where database_name in
(select name from sys.databases where recovery_model_desc='FULL')
group by database_name,type
order by database_name,type
if exists (select 'x' from sysobjects where name ='final_table')
begin
drop table final_table
end
create table final_table(DBName nvarchar(500),FullBackup_date datetime, LogBackupDate datetime)
insert into final_table(DBName)
selecT distinct Database_name From temp_tbl
update x
set x.FullBackup_date=y.backupdate
from final_table x,temp_tbl y
where x.DBname=y.database_name
and y.type='D'
update x
set x.LogBackupDate=y.backupdate
from final_table x,temp_tbl y
where x.DBname=y.database_name
and y.type='L'
set @xml= (select CAST((
selecT DBName as 'td', '',
FullBackup_date as 'td', '',
LogBackupDate as 'td'
FROM final_table where LogBackupDate < (convert (varchar(30), (dateadd(hour,-2,getdate())),121))
for xml path ('tr'),elements) as nvarchar(max) )
)
SET @body =
'<h3> Databases not backed up for last 2 hours </h3>
'
begin
SET @body = @body + @xml + '<table border="1"><tbody><tr> <th> DBName </th><th> FullBackup_date </th><th> LogBackupDate </th></tr></tbody></table>'
declare @subject varchar(250)
select @subject = 'Warning: Databases has no log backup for last 2 hours'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBAs',
@recipients = 'anjum.sumaiyas@lowes.com',
-- @subject = N'Databases not backed for last 2 hrs',
@body = @body,
@body_format ='HTML',
@subject = @subject;
END
set nocount off
What I have tried:
i am getting output as such
DBName FullBackup_date LogBackupDate
DB1 2019-04-01T06:21:25 2019-04-01T06:52:31
DB2 2019-02-18T07:08:47 2019-04-01T06:52:29
DB3 2019-02-18T07:08:25 2019-04-01T06:52:08
Further to the OP comment above... try replacingset @xml= (select CAST(( selecT DBName as 'td', '', FullBackup_date as 'td', '', LogBackupDate as 'td' FROM final_table where LogBackupDate < (convert (datetime, (dateadd(hour,-2,getdate())),121)) for xml path ('tr'),elements) as nvarchar(max) ) )with
set @xml= (select CAST(( select DBName as 'td', '', FORMAT(FullBackup_date, 'yyyyMMdd HHmmss') as 'td', '', FORMAT(LogBackupDate, 'yyyyMMdd HHmmss') as 'td' FROM final_table where LogBackupDate < dateadd(hour,-2,getdate()) for xml path ('tr'),elements) as nvarchar(max) ) )Points to note - you've stated that you are using a
datetimetype so get rid of the conversion of adatetimeto avarcharin theWHEREstatement
If you don't want the date in that specific format try using one of the standard formats - see this list SQL Server Date Format Cheatsheet | TablePlus[^]
Given that this is an email for a human rather than for a system your users might prefer something like format 113 e.g.SELECT CONVERT(VARCHAR(26), GETDATE(), 113)displays as
10 Apr 2019 15:21:50:670
这篇关于在SQL中转换datetime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
