sql query date format

[JY_11008628]

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

[VladimirRosu]

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]

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

[Constantine · Accepted solution]

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

[vnamboodheri]

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.