Click here to Skip to main content
14,386,783 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Posted
Updated 10-Apr-19 5:23am
v2
Comments
digimanus 9-Apr-19 8:48am
   
HINT: SELECT format(@datetime,'yyyyMMddHHmmss')
Anjum18 9-Apr-19 9:12am
   
Thanks @digimanus! but it dint work!
CHill60 9-Apr-19 9:52am
   
Digimanus will not know you have responded unless you use the "Reply" button to his post. I have just tried
SELECT FORMAT(getdate(), 'yyyyMMddHHmmss')
and got the result 20190409144603 so his solution does work
CHill60 9-Apr-19 9:44am
   
Why do you want to convert it? Why on earth are you converting datetime into varchar? Especially in a WHERE clause! It's a valid datetime, you are interested in datetime so leave it as … datetime
Anjum18 9-Apr-19 10:02am
   
Hey Chill60,

I just want my output to look without 'T'. Any help?
CHill60 9-Apr-19 12:06pm
   
Do it in the GUI layer not in SQL. That doesn't answer the question as to why you are converting DateTime into a Varchar in the WHERE clause.

I only saw this by accident - see my comment to your reply to @Digimanus - use the "Reply" link so that people are notified of your response.
Maciej Los 9-Apr-19 13:07pm
   
5ed!
Anjum18 10-Apr-19 9:54am
   
Thank you! i have changed the data type to datetime. the current output looks good but as a standard i need to follow the timestamp without 'T' in my output. Also i am getting my output properly when i run it in SSMS but in email i am getting 'T' in between the date and time.
If you can edit my below script and give me the output in 'YYYYMMDD HHMMSS' format...

declare @xml nvarchar(max)
declare @datetime datetime
declare @now nvarchar(max)
declare @body nvarchar(max)

set language ENGLISH

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 (datetime, (dateadd(hour,-2,getdate())),121))
for xml path ('tr'),elements) as nvarchar(max) )
)

SET @body =
'

Databases not backed up for last 2 hours



'
begin

SET @body = @body + @xml + ' DBName FullBackup_date LogBackupDate '

declare @subject varchar(250)
select @subject = 'Warning: Databases has no log backup for last 2 hours'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA',
@recipients = 'WWW@yyy.com',
-- @subject = N'Databases not backed for last 2 hrs',
@body = @body,
@body_format ='HTML',
@subject = @subject;
END


set nocount off

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Further to the OP comment above... try replacing
set @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 datetime type so get rid of the conversion of a datetime to a varchar in the WHERE statement

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
   
Comments
Anjum18 10-Apr-19 11:03am
   
Thank you so much, Chill60! That really worked.
CHill60 10-Apr-19 12:27pm
   
Fantastic. Happy to have helped. If you mark my solution as "Accepted" that will signal to other members that the problem has been resolved

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100