Skip to main content
M
mariaSutton12-Amethyst
12-Amethyst
December 8, 2023
Question

How to format datetime in Report

  • December 8, 2023
  • 2 replies
  • 3358 views

Hi, 

I noticed that the columns with dates and time are not formatted the same in my report. Is there a way to format date and time properties in a Report Template in Query Builder?

 

I thought maybe a database function or a java method would help, but I can not figure out which. 

 

Thank you,

Maria

2 replies

A
aaronjlarson14-Alexandrite
14-Alexandrite
December 8, 2023

It would be a huge improvemnt to have a basic datetime formatting DB function, but sadly does not exist to my knowledge (unless in version beyond 11.1).  What you have to do is use concatenate with say, getyear([date]) "-" getmonth([date]) "-" getday([date]) if you wanted a format like "2023-12-08" from a full datetime.  I've also done substring(tocharacter([date]),1,10) which converts the date to a string and then pulls the first 10-characters starting with the first character.  Either way works.  The first gives you more flexibility in how you want to show the date.

M
mariaSutton12-AmethystAuthor
12-Amethyst
December 8, 2023

Thanks, @aaronjlarson! 
Do you know how to prevent the formatting from changing? In the image below you can see most of the data columns have the same formatting but the "Date Completed" column is different.

mariaSutton_1-1702043213498.png

 

 

A
aaronjlarson14-Alexandrite
14-Alexandrite
December 8, 2023

No, I don't know of a way to have those displays "normalized" in the generic, untouched, query builder report.  Honestly I don't even know why they're stored differently in the DB.  That's also an assumption - the fact they're stored differently.  Truth is, most of my interest regarding date/time stamps are only on the date itself for reporting purposes.  I typically only get into actual timestamps when I'm doing a forensic investigation of something that "went wrong" in a workflow process or something.  

I
IanW10-Marble
10-Marble
December 8, 2023

Another way to format the date in your report is to do the below:

 

IanW_0-1702044981388.png

You can use a - or / in the format as well.

    M
    mariaSutton12-AmethystAuthor
    12-Amethyst
    December 8, 2023

    @IanW, this is very helpful! 
    Do you have a link for "To Character" documentation that you can share?
    I 'd would like to see how time zone or 24h time options are handled.

    Thank you

    I
    IanW10-Marble
    10-Marble
    December 8, 2023

    Here is the link to where I found this solution.

     

    https://www.ptc.com/en/support/article/CS139088?source=search

      Terms & ConditionsCookie settingsAccessibility statement