Date only selection in reports

[Ashley]

I have been working on what I thought would be a simple improvement to RELEASE.  This is to add sign off date and post sign off date and then create a report to list all releases signed off on a certain date or between dates.

 

I have only one custom date field left and have posted on the forum, with mixed feed back as to the best way to do this.  I have tried using various custom fields with varchar, text or the one date field I have left.  However when I use any of these type of fields in a report and select between  or equals then nothing is shown.  Even the one date type field doesn't display anything based on a date that I know is in the system.  I have other reports using custom date fields populated via Intelligent capture forms and the report fine based on selection (although these have time as well as date).  So it just seems to be an issue where I use a custom field to store a date from a business process, adjust its format via ibridge and then store it in a custom field varchar, date or other, that is then used in a report and on using the date selector displays blank data.  I notice the date selector in reports does include time.  Is this a problem, as I only want the date?  There doesn't seem to be a date selector.  Unlike on the forms, where a date only format is possible.

[SamS]

Hi @Ashley,

I believe you are very close to realising why what you are trying to do isn't working the way you expect.

You are storing a date (with or without time; that is relatively unimportant at this stage) in a VARCHAR (text) field. This means that the DB doesn't "know" that the data contained is a DATE. The db (and thus the report) will treat that data as text.

If you are using the report's date selector to fill in the date(s), the report engine will convert that to a DB date before presenting it to the DB which then will compare it (the selected DB date) to your text field.

The only way this method stands a chance of working would be if the text field is exactly formatted as the DB datetimestamp - that way you are effectively fooling the DB in mistaking your stored text to be a valid DB date. I would not recommend doing this as the "knowledge" that this is hacked will likely be lost at some stage in the future and would be difficult to maintain going forward.

 

[Ashley]

Yes I thought may be the case.  However, in a previous post I was advised to use varchar in reports when formatting a date field.  As I said I did also try with the one date field I have left, but that didn't search correctly when via a report.

 

 

 

 

Above was ujsing a varch custom field A, but I also tried with custom field 25 with above and when using a report still didn't let me select dates between a range.  However, the other date fields I used vioa Intelligent capture search fine within a report.  Very strange?

 

Ashley

 

[Ashley]

custom 25 field being a date field that is...

[SamS]

Date data within a date field will and should act as a date.

Date data within a text field will only act as a date if and only if the text matches the datetimestamp format.

This is clearly not the case and the transformation you are doing on the date as you have received/stored it sets if even further away from the desired (i.e. the DB's datetimestamp) format.

Any advice to capture date data within a text field will have been to allow you to just STORE the data. The consideration that you might want to REPORT on that data (and specifically use the reporting tool for date-selection on that field; just displaying that data in a report should be fine) might not have crossed the adviser's mind (as that particular requirement might not have been specified).

With dates captured in text fields, you only really can use that data to look at - not for manipulation.

As mentioned you might stand a chance by manipulating the date into the DB's datetimestamp or you will have to modify your report to either accept some text input (as opposed to date/time selection) to compare against (given your chosen date format that is unlikely to be fruitful) or not filter on that particular field, ensure the field is visible in the (.csv) results and rely on Excel or such to "deal with"/"recognise" that date-data.