Show Date Only

[JTempleton]

Good day!

I am working on a version of the open tickes with SLA overtime report. The following fields show both time and date, I am looking for them to just show the date:

Select Top 1000000 htblticket.ticketid,
'#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
htblticket.date As CreationDate,
htblticket.updated As LastUpdated,
htbltickettypes.typename As Type,
htblticketstates.statename As State,
htblpriorities.name As Priority,
htblsource.name As Source,
htblusers.name As [User],
htblusers1.name As AssignedAgent,
htblusers2.name As UserLastNote,
'../helpdesk/icons/' + htbltickettypes.icon As icon,
htblticket.subject As Subject
From htblticket
Left Join (Select Top 1000000 htblnotes.ticketid,
Min(htblnotes.date) As FirstPublicReply
From htblnotes
Inner Join htblagents On htblnotes.userid = htblagents.userid
Where htblnotes.notetype = 1
Group By htblnotes.ticketid) As InitialResponseTime On htblticket.ticketid =
InitialResponseTime.ticketid
Inner Join htblticketstates On htblticketstates.ticketstateid =
htblticket.ticketstateid
Inner Join htbltickettypes On htbltickettypes.tickettypeid =
htblticket.tickettypeid
Inner Join htblpriorities On htblpriorities.priority = htblticket.priority
Inner Join htblsource On htblsource.sourceid = htblticket.sourceid
Inner Join htblusers On htblusers.userid = htblticket.fromuserid
Left Join htblagents On htblagents.agentid = htblticket.agentid
Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid
Inner Join htblusers htblusers2
On htblusers2.userid = htblticket.userid_lastnote
Where Case
When htblticket.slainitial < InitialResponseTime.FirstPublicReply Then 'Yes'
When InitialResponseTime.FirstPublicReply Is Null And
htblticket.slainitial < GetDate() Then 'Yes' Else 'No'
End = 'Yes' And (htblticketstates.statename = 'Open' And htblticket.spam <>
'True' Or htblticketstates.statename = 'New')
Order By htblticket.ticketid

Thanks!

[JTempleton]

Thanks guys!

As I keep creating reports, I will keep learning!

[Esben_D]

Good point. Guess I overthought it a bit.

[RCorbeil]

You don't actually need to slice & dice. Refer to this page.

I regularly need date-only output in queries I run and prefer descending order of magnitude (see ISO 8601), so use

Convert(VarChar(10), some_datetime_field, 121)

If you want US format with the century,

Convert(VarChar(10), some_datetime_field, 101)

should do the trick for you.

[Esben_D · Accepted solution]

Damn. Guess the HTML output doesn't like the convert.

An alternative solution is to just create a string by taking the day,month and year fields.

This should work:

  Convert(varchar,DatePart(mm, htblticket.date)) + '/' +
  Convert(varchar,DatePart(dd, htblticket.date)) + '/' +
  Convert(varchar,DatePart(yyyy, htblticket.date)) As CreationDate,
  Convert(varchar,DatePart(mm, htblticket.updated)) + '/' +
  Convert(varchar,DatePart(dd, htblticket.updated)) + '/' +
  Convert(varchar,DatePart(yyyy, htblticket.updated)) As LastUpdated,

[JTempleton]

One more quick question.

When I run the report in LanSweeper, those two fields only show the date. However, when the report is emailed out, the report then shows 06/20/2018 00:00:00. The report is emailed as HTML. Is there a separate command that can be used so when the report is emailed out, it doesn't show the date?

Thanks!

[Esben_D]

Converting the datetime to a date should work for you. Try replacing the date fields with the code below.

Convert(date,htblticket.date) As CreationDate,
Convert(date,htblticket.updated) As LastUpdated,

[JTempleton]

Charles.X wrote:
Converting the datetime to a date should work for you. Try replacing the date fields with the code below.

Convert(date,htblticket.date) As CreationDate,
Convert(date,htblticket.updated) As LastUpdated,

Thank you! You are awesome!