Delete back ups more than 2 weeks in sql server I am not a DBA, I need to delete the USER_DATABASE backups older than 2 weeks. Is this below stmt work for this scenario?  

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASE', 
@Directory = 'C:\Backup',      
@BackupType = 'FULL',          
@Verify = 'Y',                
@Compress = 'Y',              
@CheckSum = 'Y',
@CleanupTime = 336          


This below script is my regular backup.

declare @Date varchar(16),
        @query nvarchar(100);

select @Date = convert(varchar,Year(getdate())) + right('00' +  convert(varchar,Month(getdate())),2) + right('00' + convert(varchar,Day(getdate())),2) + right('00'+convert(varchar,datepart(hh,getdate())),2) + right('00'+convert(varchar,datepart(Mi,getdate())),2);

select @query = N'BACKUP DATABASE db_CP_Staging TO  DISK = ''D:\backups\db_C_Staging\db_C_Staging' + @Date + '.bak' + '''';

exec sp_executesql @statement = @query;


How can I add cleanup time to this query? Can some one help me on this?
Yes, it looks like you're using Ola Hallengren's backup scripts which operate in hours, so 14 days * 24 hours = 336 hours. 

Every time this is run, a full backup of the user databases will be taken and any copies older than 2 weeks will be deleted.

Edit:

Based on the OP's edit, you are fine to schedule a job executing your first script as a replacement for the second script you posted. The frequency of this will depend on your backup and restore needs for your business.

As Kris said, this delete only occurs after a successful backup unless you specify the parameter @CleanupMode = 'BEFORE_BACKUP'