Date Parameters allow end users to select Date values from a calendar rather than having to key in values. A parameter will be automatically updated to have a Parameter Type of “Date” when the parameter’s Data Type property is set to Date or DateTime. It is inadvisable to use Text or any other form of parameter when passing Date values into Datasets.

At run-time, end users will then be shown a calendar dialogue:

Using Date Parameters to filter DateTime fields:

SQL differentiates between Date fields which include only a date part and DateTime fields which hold both the date and time parts. This means that some care is required when applying Date Parameters to data held in DateTime (SQL Server) or TimeStamp (Oracle) fields which have been saved into the database with time elements (DateTime fields are often used to save date values in source system databases where the Time element is set as 00:00:00 in every cell).

As an example, if a user selects a Start Date value of ’01/01/2020’ and an End Date value of ’01/01/2020’ to be applied to data that’s held in a DateTime field, what that really means is that they want all the data from “01/01/2020 00:00:00” (midnight at the start of the day) until “01/01/2020 23:59:59” (just before midnight at the end of the day).

The solution for filtering of DateTime fields with Nathean Analytics parameters is to cast the field being filtered to a Date in your SQL e.g.

instead of

WHERE tablename.datetimefield BETWEEN :START_DATE AND :END_DATE

use instead:

WHERE CAST(tablename.datetimefield AS DATE) BETWEEN :START_DATE AND :END_DATE

Hint

The above syntax is for recent editions of MS SQL or alternative SQL dialects, make sure that you use the appropriate for the dialect. For example, the following will work with MS SQL 2005 CAST as datetime),

for MY SQL use DATE( datetimecol)

for Oracle use TRUNC(datetimecol)

Revision: 7
Last modified: Oct 31, 2019

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment