Show Date Without Time

[nhouse24]

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

[RCorbeil]

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.

[rader]

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. ¯\_(ツ)_/¯

[RCorbeil]

If your entry have date and time functions, then the standard

"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.

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

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]

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

"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...

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.

[RCorbeil]

Look up the Convert() function (Microsoft, W3Schools).

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.