Question

How to create a view to only use date part of a datetime field

  • 25 January 2024
  • 7 replies
  • 112 views
M
Rank
Userlevel 4
Badge +9
  • Miraj
  • Sidekick (Partner)
  • 59 replies

Hi Experts,

This is a follow up to a this post. I have the requirement of creating a view that extracts the date part of the plan_start_dttm (a datetime field in task), and to use this view field for filtering tasks by only giving the date (irrespective of time).

My timezone is UTC+11, and the azure database is storing values in UTC. Because of this I am running into an issue with the dates stored during morning hours (before 11 am).

 


I have highlighted above how the query yields incorrect values based on the time. Notice how for 10.59 a.m it gives the incorrect date and at 11.01 a.m it’s correct.

Is there any way around this issue?  ​​​​​​​

7 replies

Shneor Cheshin
Rank
Userlevel 6
Badge +26

Hi @Miraj 

First, I opened a support call for a similar issue. When the mask type is date and not date-time we encounter the issue. So I hope that the product team will resolve this.

Secondly, the values are still stored in the DB as date-time, and not just as date. So you might encounter the same issue when searching for dates.

For the solution -

I can think of creating a string field that will represent the date. 

Users will search for the string ‘1/26/2024’ instead of searching for the date which will contain time as well.

You might be able to populate this string value on a UDF using an expression on a business rule.

e.g. substring(task.plan_start_dttm,0,10) 

If not a business rule, our friend ‘SQL script’ will give more flexibility.

Cheers!

M
Rank
Userlevel 4
Badge +9

 @Shneor Cheshin 

Thanks for the response. The issue with the dateonly mask and its handling of the dates falling in different timezones is a really frustating bug.
We changed the field mask from dateonly to datetime to sidestep this very issue. But now we’ve run into to the issue of filtering for plan_start_dttm irrespective of the time. I gave a try with your suggested options.

Business rule with a string user_def doesn’t seem to work because it still “sees” the UTC time.
 



I believe by ‘SQL script’ you meant client scripts? Client script would pick up the client time, and probably we can do this on a screen event. But this wouldn’t be practical for adjusting for all the existing data 😒

This seems to be quite the challenge 😐

Shneor Cheshin
Rank
Userlevel 6
Badge +26

Hi @Miraj 

I feel your pain. If it comforts, we are in the same boat 😊

-- Business rule with a string user_def doesn’t seem to work because it still “sees” the UTC time.

As I wrote, you will need to cut the values. I suggested substring expression, but need to test this approach.

-- I believe by ‘SQL script’ you meant client scripts?

No. I mean creating a new custom view and joining it with the task table. The new field(s) in the view will be used for searching dates as strings.

Something like below script (you can change formats accoridng to your requirements)

CREATE VIEW MY_VIEW
AS
SELECT TASK_ID,
SELECT CONVERT(VARCHAR(10), PLAN_START_DTTM, 120) AS PLAN_START_DTTM_STR
FROM TASK

In UI designer you join the view and the task based on the task_id. Add the new field(s) to the search panel.

 

Cheers!

M
Rank
Userlevel 4
Badge +9

@Shneor Cheshin 
“As I wrote, you will need to cut the values. I suggested substring expression, but need to test this approach.”

I tested this, but still doesn’t work because the string it cuts the values from is itself incorrect. In the above screenshot I have attached you can see how the user_def11 holds the business rule string converted value, and how it is incorrect to start with (27th instead of 28th).

Same happens with the SQL query, any sql function used is applied on the server which holds datetime in UTC
 

 

😪

Shneor Cheshin
Rank
Userlevel 6
Badge +26

Hi @Miraj 

Try this with your time zone

select top 100
plan_start_dttm,
convert(varchar,plan_start_dttm AT TIME ZONE 'UTC' AT TIME ZONE 'AUS Eastern Standard Time',23) as plan_start_dttm_string
from task

 

Cheers!

M
Rank
Userlevel 4
Badge +9

@Shneor Cheshin sorry for the late reply. got distracted with some other stuff.

Thanks for that reply.

Your view would work. But as you can see, now we have to hard-code the timezone, and this isn’t really ideal. I had a similar workaround applied already, by adding 11 hours to the plan start date because I know that’s the time diff with UTC 😅.

But this doesn’t work, since within Australia itself there are different timezones from which users access the same application. So, I guess this is something that RnD will definitely have to solve, as this is such a small issue that’s causing big pain! 

For now we have reverted back to the dateonly mask to get rid of the time part altogether
 

Shneor Cheshin
Rank
Userlevel 6
Badge +26

Hi @Miraj 

I was not aware that you need to support multiple time zones.

There are solutions for that as well. The simple way I can think of is to try to populate a UDF instead of manipulating the data with a view.

Cheers!

Reply


Terms & ConditionsCookie settings