Как удалить T из таймфрейма (2017-02-27T00:00: 00)?
--
Use TestDB
Declare @date date;
select @date =
case
when datepart(weekday, getdate()) = 2 then getdate()-3
else getdate()-1
end;
Create TABLE #Salesdoc
(
Salesorg [nvarchar](30),
Id [nvarchar](30),
Phase [nvarchar] (30),
WorkerStartTime [datetime]
)
INSERT INTO #Salesdoc
select
S.Salesorg ,
S.Id,
S.Phase,
H.WorkerStartTime as "CAS TO PI TimeFrame"
From
SdoMain as S
LEFT OUTER JOIN
CssIntOutboundXmlObject as O
INNER JOIN
CssIntOutboundXMLHostRel as H
ON O.Id=H.XMLId ON O.ObjectPKey=S.PKey
AND S.Status<>'d'
Where
S.Status<>'d'
and
S.InitiationDate= @date--Or H.WorkerEndTime<= @date)
and S.ErpId <> S.Id
--and S.InitisationDate <>'2080-06-17 00:00:00.000'
and S.Phase in ('OnRoute','Submitted')
order by H.WorkerStartTime desc
--order by S.initiationdate
GO
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
--DECLARE @body1 NVARCHAR(MAX)
DECLARE @srvname varchar (100)
DECLARE @ProfileName Nvarchar (500)
DECLARE @Subject Nvarchar (500)
SELECT TOP (1) @ProfileName = name FROM msdb..sysmail_profile
SELECT @srvname = Convert(varchar,SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
SET @Subject = 'Order Stuck In Onroute state' + @srvname
SET @xml = CAST(( SELECT
[Salesorg] AS 'td','',
[Id] AS 'td','',
[Phase] AS 'td','',
[WorkerStartTime] AS 'td',''
from #Salesdoc
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><body>
<p>Hi,</p>
<p> Request you to please check the reason behind it.</p>
<table border = 1 bgcolor="#f1f2e3">
<tr bgcolor ="#0fddd3">
<th bgcolor ="#FAFAF8"> SalesOrg </th>
<th bgcolor ="#FAFAF8"> Id </th>
<th bgcolor ="#FAFAF8"> Phase </th>
<th bgcolor ="#FAFAF8"> CAS to PI TimeFrame</th>
</tr>'
--set @body1='<html><body1>
--<p>Regards,</p>
--<p>CONA Support Team</p>'
SET @body = @body + @xml +'</Table></body></html>'--</body1>
IF EXISTS (
SELECT 1 FROM #Salesdoc
)
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'sunitee28@gmail.com',
-- replace with your email address
@profile_name ='TestEmail', -- replace with your SQL Database Mail Profile
@body = @body,
@body_format ='HTML',
@subject = @Subject ;
GO
DROP table #Salesdoc
Что я уже пробовал:
В приведенном выше коде я получаю результат как
Hi , Request you to please check the reason behind it. SalesOrg Id Phase CAS to PI TimeFrame 4200 2001813984 Received 2017-02-27T00:00:00 4200 2001807588 Received 2017-02-27T00:00:00 4100 2001812218 Received 2017-02-27T00:00:00
Я хочу удалить T из CAS в Pi таймфрейм 2017-02-27T00:00: 00. пожалуйста, подскажите, как это сделать.
Также я хочу добавить подпись для этого письма.Пожалуйста, помогите, как добавить подпись в html.