CONVERT with Style 120

• jsheldon

  SSCertifiable

  Points: 5250

  More actions

  December 19, 2007 at 10:01 am

  #184840

  I have a backup log script and I want the .trn file name to include the date and time.

  This script (thanks to Adam on this board)

  DECLARE

  @SQL nvarchar(300),

  @date varchar(20)

  SET @date = REPLACE(CONVERT(varchar,GETDATE(),101),'/','')

  SET @SQL = N'

  BACKUP LOG ReportServer

  TO DISK = ''\\HQ-NAS1\SQLBACKUP$\HQ-APPS2\Log\ReportServer' + @date + '.trn''

  WITH FORMAT

  RESTORE VERIFYONLY FROM DISK =N''\\HQ-NAS1\SQLBACKUP$\HQ-APPS2\Log\ReportServer' + @date + '.trn'''

  EXECUTE sp_executesql @SQL

  Adds the date to the end of the .trn file. This works well with .BAK but not frequently backups of the transaction logs.

  So I thought for the line

  SET @date = REPLACE(CONVERT(varchar,GETDATE(),101),'/','')

  I would change the style from 101 to 120 to obtain yyyy-mm-dd hh:mi:ss

  If I do this:

  REPLACE(CONVERT(varchar(30), GETDATE(), 120),'-','')

  Yet the output from this command is

  20071219 11:59:03

  I need to remove the space between the date and time and replace the ':' with ''

  I realize this is a REPLACE WITHIN TWO MORE REPLACES just unsure how to script it.

  Thanks...

• Piotr.Rodak

  SSCrazy Eights

  Points: 9376

  More actions

  December 19, 2007 at 10:46 am

  #761482

  you take result of inner replace and put it as first parameter to outer, and so on.

  select replace(replace(REPLACE(CONVERT(varchar(30), GETDATE(), 120),'-',''), ' ', ''), ':', '')

  ...and your only reply is slàinte mhath

• jsheldon

  SSCertifiable

  Points: 5250

  More actions

  December 19, 2007 at 11:23 am

  #761498

  Thanks Piotr that works...