Notification

Icon
Error

Show Date Without Time - Configure date column to show only the date without time

Posted: Tuesday, May 4, 2021 10:39:43 PM(UTC)
nhouse24

nhouse24

Member Original PosterPosts: 6
1
Like
Hello,

I am hoping to get some help with this report. I'd like the Creation Date and Last Update Date to show as DD/MM/YYYY WITHOUT showing the time.

SELECT TOP (1000000) '#' + CAST(htblticket.ticketid AS nvarchar) AS Ticket, htblticket.date AS CreationDate, htblticket.updated AS LastUpdated, htblticketstates.statename AS State, htblpriorities.name AS Priority,
htblsource.name AS Source, htblusers.name AS [User], htblusers2.name AS UserLastNote, htblticket.subject AS Subject
FROM htblticket INNER JOIN
htblpriorities ON htblpriorities.priority = htblticket.priority INNER JOIN
htblticketstates ON htblticketstates.ticketstateid = htblticket.ticketstateid INNER JOIN
htblusers ON htblusers.userid = htblticket.fromuserid LEFT OUTER JOIN
htblagents ON htblagents.agentid = htblticket.agentid LEFT OUTER JOIN
htblusers AS htblusers1 ON htblusers1.userid = htblagents.userid INNER JOIN
htbltickettypes ON htblticket.tickettypeid = htbltickettypes.tickettypeid INNER JOIN
htblsource ON htblticket.sourceid = htblsource.sourceid INNER JOIN
htblusers AS htblusers2 ON htblticket.userid_lastnote = htblusers2.userid
WHERE (htblticket.date > GETDATE() - 7) AND (htbltickettypes.typename = N'Change Management') AND (htblticket.spam <> 'True')
ORDER BY htblticket.ticketid
RC62N
#1RC62N Member Posts: 557  
posted: 6/15/2021 9:04:47 PM(UTC)
Look up the Convert() function (Microsoft, W3Schools).
Code:
SELECT Convert(VarChar(10), htblticket.date, 103) AS CreationDate

The length of the target VarChar is optional, but I prefer to state limits where I know them. 103 specifies the "DD/MM/YYYY" date format.
rader
#2rader Member Posts: 30  
posted: 7/1/2021 12:38:22 AM(UTC)
I'm recently in the same boat, but I found that the code on certain reports leaves artifacts.
If your entry have date and time functions, then the standard
Quote:
"Select Convert(VarChar(10), htblticket.date, 103) As CreationDate"
might leave the first character in the time portion IF your date starts with a zero (0).


What I was seeing with 06/03/2021 11:15:02AM for example, the report was showing 06/03/2021 1, the last 1 being part of the time variable.

I fixed it using CAST as shown here...
Code:
Convert(varchar(10),Cast([Ordered Date].data As date),110) As [Ordered Date]


The "Cast([Ordered Date].data As date)" only allows the date component of the date/time to be processed. I also changed from 101 (mm/dd/yyyy) or 103 (dd/mm/yyyy) to 110 for (mm-dd-yyyy) for better report visibility.
RC62N
#3RC62N Member Posts: 557  
posted: 7/2/2021 3:23:23 PM(UTC)
Quote:
If your entry have date and time functions, then the standard
Quote:
"Select Convert(VarChar(10), htblticket.date, 103) As CreationDate"
might leave the first character in the time portion IF your date starts with a zero (0).

What I was seeing with 06/03/2021 11:15:02AM for example, the report was showing 06/03/2021 1, the last 1 being part of the time variable.

That should not be the case.

Per the Convert() documentation, and confirmed by what I see when I do the select, date format 103 produces zero-padded day and month values. Applying the function to your example 06/03/2021 11:15:02AM will produce "06/03/2021".

The maximum length of 10 specified on the VarChar returns up to 10 characters from the 103-format string. "06/03/2021 1" is 12 characters; that would be chopped off after the first 10. The only way I can think of that you would see extra characters is if your instance of SQL Server isn't zero-padding the day and month, i.e. it's returning "6/3/2021" instead of the expected "06/03/2021".

On top of all that, the 103 format only returns the date, no time, so even if your configuration fails to zero-pad the day and time, there would only be 8-to-10 characters returned. I have no idea where the extra " 1" might be coming from.

Code:
SELECT
  GetDate() AS RightNow,
  Convert(VarChar(10), GetDate(), 103) AS Trimmed103,
  Convert(VarChar    , GetDate(), 103) AS NoTrim_103,
  Convert(VarChar(10), GetDate(), 110) AS Trimmed110,
  Convert(VarChar    , GetDate(), 110) AS NoTrim_110,
  Convert(VarChar(10), GetDate(), 121) AS Trimmed121,
  Convert(VarChar    , GetDate(), 121) AS NoTrim_121
produces
Code:
RightNow    2021-07-02 08:01:18.983
Trimmed103  02/07/2021
NoTrim_103  02/07/2021
Trimmed110  07-02-2021
NoTrim_110  07-02-2021
Trimmed121  2021-07-02
NoTrim_121  2021-07-02 08:01:18.983

From some quick Googling, zero-padded day and month when using Convert() appears to be normal. The only results I found without zero-padded values involved people going out of their way to remove the 0s after doing the Convert().
rader
#4rader Member Posts: 30  
posted: 7/2/2021 6:57:58 PM(UTC)
That's what I thought to but that's not what I was seeing. I can't explain it completely as SQL is just a recent addition to my skills, but my code is working on the report. ¯\_(ツ)_/¯

RC62N
#5RC62N Member Posts: 557  
posted: 7/2/2021 8:29:18 PM(UTC)
Weird. Well, as long as you've figured out how to make it do what you want, I guess you can call it a win.

Active Discussions

Lansweeper Monitor history showing only last monitors
by  cross_eur   Go to last post Go to first unread
Last post: 7/23/2021 6:06:51 PM(UTC)
Lansweeper Report Login time reduces number of computers by 300
by  cross_eur  
Go to last post Go to first unread
Last post: 7/23/2021 6:05:42 PM(UTC)
Lansweeper Merging 2 reports
by  Apaulcolypse   Go to last post Go to first unread
Last post: 7/22/2021 10:02:59 PM(UTC)
Lansweeper HELP - Add Registry Key Values to Asset Report
by  Apaulcolypse  
Go to last post Go to first unread
Last post: 7/22/2021 9:26:43 PM(UTC)
Lansweeper Can I request a custom report here?
by  Brian G   Go to last post Go to first unread
Last post: 7/22/2021 7:20:56 PM(UTC)
Lansweeper List all users with E-mail address
by  Brandon  
Go to last post Go to first unread
Last post: 7/21/2021 7:06:36 PM(UTC)
Lansweeper Identifying users of Windows legacy authentication
by  Baronet   Go to last post Go to first unread
Last post: 7/21/2021 5:26:38 PM(UTC)
Lansweeper Windows Version different between reports
by  RC62N  
Go to last post Go to first unread
Last post: 7/21/2021 3:27:04 PM(UTC)