When a filter is created in Strategy Developer 9.x to get data from a specific date, users may create an attribute qualification defined as
Day(ID) exactly 05/27/2010
This filter conditionality will generate the SQL shown below :
Sql
select distinct a12.im_id im_id,
a12.pm_creation_date pm_creation_date
from VwIncident a12
where a12.pm_creation_date = `2010-05-27`
If the conditionality above is used on a column where the data is stored as a time stamp with values for the time such as '05-27-2010 12:34:56.123' , users will notice that the rows are ignored. By default, the SQL Engine interprets 'MM-DD-YYYY' as 'MM-DD-YYYY 00:00:00.000 and therefore, rows with values for time different than 00:00:00.000 are ignored.
In order to make sure that the rows with a time value are considered on the result set, the following filter condition should be used:
Day(ID) Between 'MM-DD-YYYY' and 'MM-DD+1-YYYY'.
The SQL generated by the filter above will be then changed to:
Sql
select distinct a12.im_id im_id,
a12.pm_creation_date pm_creation_date
from VwIncident a12
where a12.pm_creation_date between `2010-05-27` and `2010-05-28`
The "where" clause will return the data for all hours in the specified date.
Another approach is to convert the date element using a "Convert" function using an apply simple function. The SQL below is an example of the generated SQL using a SQL Server 2008 database:
Sql
select distinct a12.im_id im_id,
a12.pm_creation_date pm_creation_date
from VwIncident a12
where (CONVERT(varchar(11), a12.pm_creation_date, 101)) = CONVERT(varchar(11),convert(datetime, `2010-05-27`),101)
