Hi,
I have a datetime "Created On" that automatically records creation time of new record in the table.
"Created On" has the following setting:
When I try to use datetimeformat or datetimeparse to copy and convert the data of this datetime field into new date only field, I realized the converted date is not correct, as shown below:
Formula i used: datetimeformat(Created on, "YYYY-MM-DD")
I suspect that timezone is the cause of this issue, please kindly advise me if there is way or alternative way to get the date only correctly in the Data Blaze? I am using this date only information to perform the countagg function in query so I only need the date part.
Ok I found an alternative solution that works if you have a fixed timezone (e.g. +8)
You can use datetimeadd to add 8 hours to the original datetime record first before formatting it using datetimeformat.
E.g.: datetimeformat(datetimeadd(Created on, 8 , "h"), "YYYY-MM-DD")
1 Like
Hi Carls_Teo, welcome to the forum! 
I understand that you're not able to format the "created on" date with respect to the timezone. Unfortunately, we only support UTC right now with the datetimeformat function. However, we're currently working on adding support to the timezone option to the datetimeformat function, which should be available in the coming weeks, I'll let you know once this feature gets released.
Meanwhile, your workaround of using the datetimeadd is perfect, thanks for sharing it with us.
2 Likes
Ok, cool, looking forward to timezone option in date related functions. Thanks for the information!
Hi @Carls_Teo,
We've recently introduced optional parameter to datetimeformat function that allow you to specify timezone in which to display the resulting date.
For example: datetimeformat(`Created on`, "YYYY-MM-DD HH:mm", "Asia/Almaty")
You can find list of available timezones here.
2 Likes
I would like to piggyback a question to this topic. I like the US date formatting option that I can set a date column to. However, when a user interacts with the table from the DB select drop-down menu in a snippet, the dates revert back to YYYY//MM/DD. Unfortunately this causes confusion quite often and slows users down. Is there a way to change the date formatting in the DB select drop down menu? Am I missing something?
You can use BSQL to change your query and include datetimeformat
SELECT field1,datetimeformat(date field,"MM/DD") FROM ...
I tried that but it did not work. Says the formula is invalid. Here is my existing BSQL:
SELECT Start Date, Term Code FROM Start Dates & Term Codes ORDER BY Start Date ASC
Disregard. I popped this into BlazeAI and determined that an "as" command was missing. The correct code is:
SELECT datetimeformat(Start Date,"M/DD/YYYY") as Start Date, Term Code FROM Start Dates & Term Codes ORDER BY Start Date ASC
1 Like
