Datepicker also shows time

Ryan Carrie ●

Hi Peter,

Thanks for taking the time to join me on a call today. As a quick summary, it looks like this behavior is actually the behavior of the jTDS Driver for your SQL Server data source.

Our Development team has found that switching to the Microsoft 2005 Driver will fix this issue.

You can do this under 'Administration' > 'Admin Console' > 'Data Sources' > $NameofDataSource > 'Connection Settings' > 'Driver'.

Please give this a go and let me know if it resolves your CAST issue. I also recommend testing your content after this change to verify everything works as expected. I'll leave this open while you test this out and check back if I don't hear anything.

Thanks,

Ryan

1

Mahesh Thatipamula ●

Hi Peter,

Thanks for your email.

Can you please let me know the build you are using?

I have tested it on 7.4, 20180226, this issue has already been raised as a defect.

I will convert this question into ticket, and also in my video you will be able to see how to convert a timestamp field to date.

Please let me know if you have any questions.

Regards,

Mahesh

Files:

Reply URL

1

Guest ●

Hello,

I am using 7.4 20180313

In your video, at time 1:16, you can see the issue in action, where although that filter is set as a date, the time does appear in the date picker.

So just to be clear, is that being considered a defect and it should be addressed in the future? We're currently looking to switch to 7.4 from 7.1 across all of our accounts, and this is really the only sticking point left. Otherwise really liking the UI in 7.4!

Reply URL

1

Mahesh Thatipamula ●

Hi Peter,

Thanks for your reply.

I have double checked this issue and learnt that this is not actually an issue. I was using a formatter to change the format, but infact I should use the date converter and now it doesn't show me the time along with the date in the filter.

I have done a quick video and hope this helps.

Please let me know if you have any questions.

Files:

Reply URL

1

Guest ●

Hi Mahe,

When I click the + that you do in your video, I do not get the same options, I only get the option to create a calculated field. So, that solution does not work for me, although I am not sure why I do not get the same options as you.

Any thoughts?

URL

1

Guest ●

Ah I think you were editing a view and not a report, correct?

URL

1

Mahesh Thatipamula ●

Hi Peter,

Yes, I was editing a View and not a report.

Please let me know if you have any questions.

Regards,

Mahesh

Reply URL

1

Guest ●

That did it! Thanks, this solved a months old problem! Really appreciate your help.

URL

1

Mahesh Thatipamula ●

Thanks Peter.

I am glad this issue is now resolved.

I will mark this ticket as completed.

Please let me know if you have any questions on this ticket.

Regards,

Mahesh

Reply URL

1

Guest ●

Mahe,

Although this was a good initial solution, if I create a view from freehand SQL, the option to create a date function does not exist.

I thought I would be able to change my SQL to cast or convert a datetime into a date, but when the data gets pulled in, a datetime cast or converted always appears in YF as a varchar.

About half of our views are made from freehand SQL - can you please re-open this?

Thanks

URL

1

Guest ●

Hello Mahesh,

Any thoughts?

Peter

Reply URL

1

Mahesh Thatipamula ●

Hi Peter,

Thanks for your reply. This ticket is in progress.

Can you please send me the SQL you have written to convert a DATETIME to DATE format while creating a View?

I will try the same and see if I can find a solution.

Let me know if you have any other questions.

Regards,

Mahesh

Reply URL

1

Guest ●

select

cast(dd.FullDate AS Date) as "Scheduled FullDate", -- THIS LINE RIGHT HERE

wi.WorkItemKey as "Scheduled Work ID",

wicsum.MinCompletedWorkId as "MinCompleted Work ID",

wicsum.CountWIC as "Work Completed Segment Count",

-- More after this but none of it is relevant to the conversation. SQL is valid. But the 'Scheduled FullDate' Field shows up as a varchar, not a Date.

URL

1

Mahesh Thatipamula ●

Hi Peter,

I have done a quick test using a freehand SQL with the Cast function.

Please look at this quick video and let me know if this is different to what you are seeing on your side.

Let me know if you have any questions.

Regards,

Mahesh

Files:

Reply URL

1

Guest ●

Mahesh,

When i attempt to cast in the same way, we do not get the same result. I have attached a screenshot which shows the dialogue I get at 44 seconds into your video. you'll note that the Format is 'Text' instead of date.

Files:

Reply URL

1

Mahesh Thatipamula ●

Hi Peter,

Thanks for screenshot.

May I know the database you are using and the data type of the date field, is it a Date type or a Timestamp?

Can you change the format from 'Text' to 'Date' format?

I will need bit more screenshot starting from the view to the report how that field is transformed from timestamp to date.

Please let me know if you have any additional questions.

Regards,

Mahesh

Reply URL

1

Guest ●

Hi Mahesh,

Sorry but I am having a hard time finding the time to get all this information.

Is there a possibility to do a screen share at some scheduled meeting time, and show you what I am experiencing? I am really hoping that it is just me not using the product correctly, but it looks more and more like a bug at this point.

URL

1

Larry Beasley ●

This is an interesting thread. Are you using PostgreSQL? I think that you are. I'm curious to know because I imagine that when you cast as a date the output is actually a string. The easiest way to tell if you are on PostgreSQL database is to see if you can cast Try using the function to_date and specify the format. to_date(dd.FullDate, 'yyyymmdd') should resolve your issue. YellowfinBI probably sees cast using dd.FullDate::Date and CAST(dd.FullDate AS Date) as string because the output will produce the date as "Apr 20 2018".

If to_date works then this is definitely a problem with YellowfinBI not knowing how to handle PostgreSQL Original Style date data type and should be raised as a defect.

URL

1

Guest ●

Hi Larry,

We are using SQL.

I think I can show this in relatively short amount of time - do you have any availability today?

Thanks,

Peter

URL

1

Larry Beasley ●

Hi Peter,

I don't work for Yellowfin. I'm just a friendly problem solver. I was hoping that the issue was DBMS related but since you indicated that you are using SQL (MS SQL Server?) then my hands are tied. When I cast getdate() as date I get the proper formatting. Sorry.

Another thing to note is that while it appears that both Mahesh's and your default date format is set to Day/Month/Year, the textbox that displays the date range shows something else. Maybe unrelated, maybe not. There may be a glitch, perhaps checking those settings and saving them again will fix that but it was a weird observation.

4435e8c0cbb6d5b2ff4635b20bd3bec0

Lastly, I typically create a calculated field in the report, cast the datetime column as date and then place the calculated field in the filter section. I've never had an issue with this. I used to do a lot of freehand SQL views also but I realized that there's no benefit. Now I simply create a virtual table as a much better option. Doing so gives you all available features as the drag and drop method.

URL

1

Guest ●

Ah, my apologies. Yes, I had also considered going through and using the drag and drop editor to fix all our views. But we have well over 60 views and it would take me some time.

Further, trying to convince the rest of my company to move from 7.1 to 7.4 - not being able to format the date in a filter is a dealbreaker for upgrading. So, I'm really hoping this will get marked as a defect and fixed.

Really appreciate the help though Larry.

URL

1

Mahesh Thatipamula ●

Hi Peter,

Thanks for your reply.

Yes, we can arrange a quick screen share. As I am in Australia timezone, I am assigning this ticket to the US team. Our team member will be in contact with you.

Please let me know if you have any concerns.

Regards,

Mahesh

Reply URL

1

Guest ●

I ended up making a video showing the issue. See attached.

Files:

Reply URL

1

Ryan Carrie ●

Hi Peter,

I'll be taking this over since I'm in the North American office in Mountain Time zone.

I'd like to start by summarizing your current issue as I understand it: When performing a CAST to convert a datetime field to a date field, the result is being pulled in as a varchar in your Freehand SQL View.

I'm happy to attempt replicating this to raise as a Defect. Please advise the type and version of RDBMS you are creating this View upon, i.e. SQL Server 2014.

This will help to ensure I can properly replicate your issue.

Thanks,

Ryan

Reply URL

2

Ryan Carrie ● ●

Hi Peter,

Thanks for taking the time to join me on a call today. As a quick summary, it looks like this behavior is actually the behavior of the jTDS Driver for your SQL Server data source.

Our Development team has found that switching to the Microsoft 2005 Driver will fix this issue.

You can do this under 'Administration' > 'Admin Console' > 'Data Sources' > $NameofDataSource > 'Connection Settings' > 'Driver'.

Please give this a go and let me know if it resolves your CAST issue. I also recommend testing your content after this change to verify everything works as expected. I'll leave this open while you test this out and check back if I don't hear anything.

Thanks,

Ryan

Reply URL

2

Guest ●

Hi Ryan,

Yes, that did seem to resolve it. I will do further testing to ensure it works as expected for other aspects of the report.

Reply URL

2

Ryan Carrie ●

Hi Peter,

I'm glad to hear it! I'll leave this open during your evaluation process. Looking forward to your results.

Thanks,

Ryan

Reply URL

1

Ryan Carrie ●

Hi Peter,

I wanted to check in and see how it's all going.

Thanks,

Ryan

Reply URL

1

Guest ●

The work around seems to have addressed the problem without any other issues we can identify.

Thanks for the follow up Ryan.

URL

1

Ryan Carrie ●

Hi Peter,

Thanks for the confirmation. I'll go ahead and mark this as Resolved as per the recommendation of changing drivers. Please don't hesitate to reach out with further questions or issues.

Thanks,

Ryan

URL

Datepicker also shows time

Replies (17)