How to make Date List-of-Values in Prompts to display only DATE instead of complete TIMESTAMP in Business Objects

If you use prompts asking end-user to enter the dates, you may have faced this issue with Business Objects Date LOVs (List-of-Values). LOVs show complete timestamp (MM/DD/YYYY HH:MM:SS AM/PM) instead of just date (MM/DD/YYYY) even though date is not defined as timestamp in your database.

Following example explains the problem as well as the solution:-

1) Time Id is stored as timestamp in the database; however I use SQL-Server convert function to change it to the date. Now I create a simple report with this single object and use it as prompt as well. The list of values (LOVs) for prompt is still showing the timestamp along with the date.

2)   One of the solutions that many forums suggest is to convert the date prompted in character format. However this leads to three problems: - 1) The “WHERE” condition will give you wrong results. 2) You will loose ability to use calendar while selecting a custom date. 3) I get wrong results as the dates in ranges give incorrect results.

3)   My requirement is to show date list of values as dates without timestamp as well as not to loose ability to use calendar while selecting a custom date. Off course correct results too!!

4)   I create one more object in the Universe and name it as Time Id Prompt. In this object I will convert the date into character and use required appropriate format. 

5)   Now I go to properties tab and name the list of values as TIMEIDPRMPT.

6)  Now edit the list of values, go to SQL for the List Of Values and you should see something like below :-

Change the list of values SQL so that you can order the list of values in ASC/DESC order. Don’t forget to check the button of not generating SQL again:-

7)  Now go to your original object Time Id and just link the list of values by List Name as follows:-

This will ensure that your list of values is actually pointing to Time Id Prompt List Of Values.

8)   Export the changes and generate a sample report :-

9)  So this helps you accomplish three things:-

a)    Do not display timestamp along with list date of values in Business Objects.

b)   Still have the ability to pick custom date from calendar.

c)    Your results are not wrong as the underlying value is still in date format.