日期时间格式不执行应该执行的操作

Question

所以我有一个datetime列，我想在我的报告上转换为smalldatetime。我期待的是这样的内容:

2011-07-13 09:51:21.087 to 2011-07-13 09:51:21

简单对吧?那么datetime列返回一个T而不是一个空格，所以我认为转换应该删除这一点，但无济于事……想法?

SELECT LockoutDate
CONVERT(smalldatetime,LockoutDate)[Small Date]
       LockoutDate         |       Small Date
2011-07-13T09:51:21.087    |   2011-07-13T09:51:00

那个"T"是什么，为什么在那里?同样，实际列是datetime而不是nvarchar之类的。

编辑:这是我正在编写的代码。

    DECLARE @tableLockPass NVARCHAR(MAX);
SET @tableLockPass = --Statement to check active accounts that are locked
        N'<H3>Locked SQL Accounts</H3>' +
        N'<table border="2" style="font-size:12px">' +
        N'<tr>' +
            N'<th>Instance</th>' +
            N'<th>Instance Purpose</th>' +          
            N'<th>Domain Account</th>' +
            N'<th>Name</th>' +
            N'<th>Account Lock</th>' +
            N'<th>Lockout Time</th>' +
        N'</tr>' +
            CAST ((
                SELECT
                [td/@align] = 'center',
                td = [Instance], '',
                [td/@align] = 'center',
                td = [Instance Purpose], '',                
                [td/@align] = 'center',
                td = Name, '',
                [td/@align] = 'center',
                td = [Display Name], '',
                [td/@align] = 'center',
                td = [Account Lock],  '',
                [td/@align] = 'center',
                td = [Lockout Time], ''
                FROM(
                    SELECT Name
                            ,ADDisplayName [Display Name]
                            ,Instance
                            ,InstancePurpose [Instance Purpose]
                            ,replace(IsLocked,1,'In Place') as [Account Lock]
                            ,convert(smalldatetime,LockoutTime,120) [Lockout Time]
                        FROM dbo.Security
                        WHERE IsLocked = 1
                    ) AS DS
                ORDER BY Instance, [Display Name]
                        FOR XML PATH('tr'), TYPE
                    )AS NVARCHAR(MAX) ) +
        N'</table>';
IF @tableLockPass IS NOT NULL
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'SQL_DBMail',
            @importance = 'NORMAL',
            @recipients = '',   
            @subject = '',
            @body = @tableLockPass, 
            @body_format = 'HTML';

我不知道这是不是一个弱代码。我仍然是相当新的SQL，所以这是我扔在一起的东西。

结果是一个非常小的表，像这样:

实例[实例用途]名称[帐户锁定][锁定时间]

DEV5通用使用BMMcClure In Place 2011-07-13T09:51:00

Answer 1

T只是一种标准的(ISO 8601)分隔时间的方法。

为CONVERT函数添加STYLE，指定如何翻译表达式:

SELECT LockoutDate, 
CONVERT(smalldatetime,LockoutDate,120) [Small Date]

看到更多

编辑:在你的完整代码

   DECLARE @tableLockPass NVARCHAR(MAX);
SET @tableLockPass = --Statement to check active accounts that are locked
        N'<H3>Locked SQL Accounts</H3>' +
        N'<table border="2" style="font-size:12px">' +
        N'<tr>' +
            N'<th>Instance</th>' +
            N'<th>Instance Purpose</th>' +          
            N'<th>Domain Account</th>' +
            N'<th>Name</th>' +
            N'<th>Account Lock</th>' +
            N'<th>Lockout Time</th>' +
        N'</tr>' +
            CAST ((
                SELECT
                [td/@align] = 'center',
                td = [Instance], '',
                [td/@align] = 'center',
                td = [Instance Purpose], '',                
                [td/@align] = 'center',
                td = Name, '',
                [td/@align] = 'center',
                td = [Display Name], '',
                [td/@align] = 'center',
                td = [Account Lock],  '',
                [td/@align] = 'center',
         td = convert(smalldatetime,[Lockout Time],120), ''
                FROM(
                    SELECT Name
                            ,ADDisplayName [Display Name]
                            ,Instance
                            ,InstancePurpose [Instance Purpose]
                            ,replace(IsLocked,1,'In Place') as [Account Lock]
                            ,convert(smalldatetime,LockoutTime,120) [Lockout Time]
                        FROM dbo.Security
                        WHERE IsLocked = 1
                    ) AS DS
                ORDER BY Instance, [Display Name]
                        FOR XML PATH('tr'), TYPE
                    )AS NVARCHAR(MAX) ) +
        N'</table>';
IF @tableLockPass IS NOT NULL
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'SQL_DBMail',
            @importance = 'NORMAL',
            @recipients = '',   
            @subject = '',
            @body = @tableLockPass, 
            @body_format = 'HTML';