cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Query Builder - Date and Time - Difference

Participant

Query Builder - Date and Time - Difference

I have to create a very simple database function to get day and month only from the date. But there is always one day lag. I will explain this in a simple way here.

 

I have created a database function as below.

1.PNG

 

It should result in two columns of same value exactly.

 

But the result is

2.PNG

 

How to resolve this?? If this is resolved, my core problem also will be resolved.

 

 

I have timezone of  GMT+05:30 in site\utilities\preference\attribute handling\local time zone

 

This timezone is same in Server machine and in my oracle database.

Tags (1)
2 REPLIES 2

Re: Query Builder - Date and Time - Difference

Hi,

Did you get the solution .Please let me know how to do it

Thank you in advance,

Susheel

Re: Query Builder - Date and Time - Difference

I have same issue on test machine and I'm using EST.

 

to_date is a string function that's taking your date and converts it to a date, though it still has its time values (setting its hours to 00:00:00).  However, when WIndchill wants to display it, it uses that date as a GMT date and gives out the GMT date converted to your time.  As you notice, its still giving you the hours and timezone in the result.

 

You therefore need to strip the time data from the object when you want to display it.  I tried 2 approaches

1. convert using To_Date function, then get substring of first 10 characters.

2. just get substring of first 10 characters from date.

 

As you can see, this results in 2 different outpus, though the second on formats it according to your standards.  I also tried out concatenating it with another string, and that works also, though you don't need the To_Date function.

date qml results.GIFHere is the Query

date qml.GIF