如何从时间范围中删除T（2017-02-27T00：00：00）？ [英] How do I remove T from timeframe(2017-02-27T00:00:00)?
本文介绍了如何从时间范围中删除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
我尝试过：
在上面的代码中，我得到的结果为
What I have tried:
In the above code,I am getting the result as
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
我想从CAS删除T到PI TimeFrame 2017-02-27T00：00： 00.Please建议怎么做。
我也想为这封邮件添加签名。请帮助如何在html中添加签名。
I want to remove T from CAS To PI TimeFrame 2017-02-27T00:00:00.Please suggest how to do so.
Also I want to add signature for this mail.Please help how to add signature in html.
推荐答案
对于日期格式，只需使用CAST / CONVERT格式化日期（通常在表示层中执行此操作） - 请参阅 SQL Server帮助程序 - 提示和技巧 - 日期格式 [ ^ ]
对于签名，你不能只将它添加到@body的末尾因为你设置了@ body_format ='HTML'
For the date format just use CAST/CONVERT to "format" the date (you would normally do this in the presentation layer) - see SQL Server Helper - Tips and Tricks - Date Formats[^]
For the signature can you not just add that to the end of @body as you have set @body_format='HTML'
这篇关于如何从时间范围中删除T（2017-02-27T00：00：00）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
