How to format datetime in Report

[mariaSutton]

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

[aaronjlarson]

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.

[mariaSutton]

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.

[aaronjlarson]

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.  

[IanW]

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

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

[mariaSutton]

@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

[IanW]

Here is the link to where I found this solution.

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

[aaronjlarson]

The 'To Character' and all other db functions are outlined in the WC Help.

https://support.ptc.com/help/windchill/whc/whc_en/#page/Windchill_Help_Center%2FQBSelectOrConstrainDatabaseFunction.html 

[mariaSutton]

@aaronjlarson  & @IanW, 
Last questions, how can the region/time zone be included when using database function "To Character".
So far I have the following but this does not include the region. I would like the result to look like "2022-07-11 13:20 EDT".

[mmeadows-3]

Have you set up the local time zone on your Windchill instance?
https://www.ptc.com/en/support/article/CS32504
According to the article, all user requests use the same time zone, based on the server configuration, regardless of where the user is located,  Windchill converts the localized time to GMT and all values in the database, logs, etc. are stored as GMT values.

Not sure what value time zone provides since it is based on the server's location and consistent for all entries in the database.  Could just append static text to the end of the date-time field based on the location of the Windchill server.