Convert datetime in a SQL

Question

0.00/5 (No votes)

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.

SQL

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 9-Apr-19 2:42am

Anjum18

Updated 10-Apr-19 4:23am

Herman<T>.Instance

v2

Add a Solution

Comments

Herman<T>.Instance 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2019-04-10T04:23:00

Further to the OP comment above... try replacing

SQL

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

SQL

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