So you have a datamodel with a SQL Statement and in the where clause you have 2 predicates?
blah_date >= :p_StartDate and
blah_date <= :p_EndDate
This was my initial approach but then since the date formats are different how will the comparison work
one format is 7/18/2013 00:00:00 and other format is 2013-09-16T08:00:00.000+01:00
I dont think it will work
Did you set the property on the 2 parameters to Date?
Did you try it?
What is your source database?
Yes and SQL Server 2008
so yes to the setting property...
Did you try it? What happens?
Yeah i already tried it the data doesn't get filtered based on date
Have you reviewed
4. Enter the Date Format String. The format must be a Java date format (for example, MM-dd-yyyy).
You set the format of the source database
you could also try
blah_date >= TO_DATE(:P_DATE_START,'MM/DD/YYYY HH:MI:SS AM') AND
blah_date <= TO_DATE(:P_DATE_END,'MM/DD/YYYY HH:MI:SS AM')
I have the date format set properly I am already thru this
I do not want to hard code the date the date is selected by the user thru the parameter (Calender format)
Also BIP stores the timestamp as in this format: 2013-09-16T08:00:00.000+01:00
I did not suggest hard coding date. Rather casting the prompted dates into the format you need for SQL Server
How do i do it?
if i am understanding your suggestion correctly then in this case ur blah_date is the date coming from column in the db table its format is (7/18/2013 00:00:00 ) in my case and P_start_Date and P_end_date are the parameters which store the date in this format (2013-09-16T08:00:00.000+01:00) coming from BIP depending on what the user selects
I dont think it will work Can you formulate the query for me
the name of the column from db is review_date
and 2 parameters start_review_date and end_review_date coming from the BIP user selects the values and it gets stored in these 2 params and compare it with review_date column....Gimme the query...?
so are your parameters in the datamodel
P_start_Date and P_end_date as indicated in second paragraph
start_review_date and end_review_date as indicated in your 4th paragraph.
What would your SQL look like of the selected review_date range was 1/1/2013 to1/21/2013 ?
start_review_date and end_review_date are the parameters defined by me (Calender)
review_date is of type date in the db table