Skip to main content
J
JY_110086285-Regular Member
5-Regular Member
September 9, 2025
Solved

sql query date format

  • September 9, 2025
  • 4 replies
  • 658 views

helppp. i have an SQL query in a Thing. Im trying to extract the date from datetime column using Cast but its not working.

 

select 
b.sysdat,
    cast (b.sysdat as date),
    a.itmcod ITEM_CODE,
    d.itmdsc ITEM_DESC,
    b.trntyp,
    sum(b.trnqty) over (partition by a.itmcod,b.trntyp) QUANTITY
from sysdtl a join systrn b on a.trnseq=b.trnseq
join itmmst d on a.itmcod=d.itmcod
 
JY_11008628_0-1757383849506.png

 

Best answer by Constantine

Hello,

 

Don't do that. It will almost certainly introduce issues related to timezones and DST, which you'll hate fixing. Instead try to pass those timestamps in complete ISO format (with date, time and zone), and format as dates only when you present them to the end user, e.g. in a mashup or in a CSV file.

 

/ Constantine

4 replies

V
VladimirRosu_11662719-Tanzanite
19-Tanzanite
September 9, 2025

Hi @JY_11008628 ,

 

Can you please post the result of the same query executed by your DB's SQL manager ?

I am asking this because I would like to see with certainty that the SQL statement is correct (ThingWorx does not have a DATE basetype, as I presume you are already aware, but the SQL DATE I would expect to at least like 2025-09-04 12:00:00 etc)

    Rocko
    Rocko19-Tanzanite
    19-Tanzanite
    September 9, 2025

    Choose your flavor. Mind the timezone.

     

    SELECT now() d1,date(now()) d2, now()::date d3, date_trunc('day', now()) d4, TO_CHAR(now(), 'DD/MM/YYYY') d5

    Rocko_0-1757402934768.png

     

      C
      Constantine18-OpalAnswer
      18-Opal
      September 15, 2025

      Hello,

       

      Don't do that. It will almost certainly introduce issues related to timezones and DST, which you'll hate fixing. Instead try to pass those timestamps in complete ISO format (with date, time and zone), and format as dates only when you present them to the end user, e.g. in a mashup or in a CSV file.

       

      / Constantine

      V
      vnamboodheriCommunity Manager
      Community Moderator
      September 16, 2025

      Hello @JY_11008628,

       

      It looks like you have some responses from some community members. If any of these replies helped you solve your question please mark the appropriate reply as the Accepted Solution. 
      Of course, if you have more to share on your issue, please let the Community know so other community members can continue to help you.

      Thanks,
      Vivek N.
      Community Moderation Team.

      Terms & ConditionsAccessibility statement