CODEWITHSUNDEEP
sql-servert-sqldatetimesmalldatetime
user2280319
user2280319

Reputation: 15

Datetime format doesn't do what is supposed to do

So I have a datetime column that I want to convert to a smalldatetime on my report. What I am expecting is something like:

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

Simple right? Well the datetime column is returning a T instead of a space so I figured that the conversion should remove this but to no avail...thoughts?

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

       LockoutDate         |       Small Date
2011-07-13T09:51:21.087    |   2011-07-13T09:51:00

What is that 'T' and why is it there? Again the actual column is datetime not nvarchar or anything...

EDIT: Here is the code I'm working on.

    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';

I don't know if this is a weak code or not. I'm still fairly new to SQL so this is something I threw together.

The result is a pretty little table like this:

Instance [Instance Purpose] Name [Account Lock] [Lockout Time]

DEV5 General Use BMMcClure In Place 2011-07-13T09:51:00

Upvotes: 1

Views: 353

Answers (1)

Horaciux
Horaciux

Reputation: 6487

The T is just a standard (ISO 8601) way to delimit the time.

Adding STYLE to CONVERT function to specify how to translate the expression:

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

See More

EDIT: On your complete code

   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';

Upvotes: 2

Related Questions