How to get Time from date Object.

[Former Member]

Hello everyone

I have a date stamp objects on which , I am getting value like :

01/05/2015 22:15

06/04/2015 16:24

My requirement is to filter the webi Report on the basis of two parameter like :

Enter the date  & Enter the Time

For Example : if i enter date 12/10/2015 and time is : 09 then only thouse records will come which are existing on this selection criteria.

But i have only onedate stamp objects which haveing both date & Time value .

So Please suggest me how i can filter my records based on two parameter , from universe or from webi and solution will work for me.

I am using BO 3.1.

Thanks in advance.

[nscheaffer · Accepted solution]

I my view you would be better off doing this in the universe rather than at the WebI report level. The reason being that to do it in WebI you would not be able use a query filter. So you would have to bring back more data than necessary and then apply a report filter.

You didn't mention what type of database your universe is based upon. I am most familiar with SQL Server so I am going to use that in my explanation. If you are using a different database you will have to adjust the syntax accordingly.

You could you use the CONVERT function and specify whatever "style" you prefer to get the date and time separate.

CAST and CONVERT (Transact-SQL)

It should be noted that these are now strings. So if you have a prompt for the date in WebI you will not be presented with a calendar to choose it.

You could convert back to a DATE or even DATETIME like this and then you will have the appropriate datatype such that you will get a calendar with your prompt.

I don't remember the exact situation, but I have had trouble with the DATE datatype. So I have always just stuck with DATETIME.

Hope this helps you out.

Noel

[nscheaffer · Accepted solution]

I just found that you can also convert to a TIME datatype in SQL Server. Check this out...

SQL SERVER – Get Time in Hour:Minute Format from a Datetime – Get Date Part Only...

Noel

[Former Member · Accepted solution]

Hi Scheaffer

thanks for your replay .

I don't have database access , I can change only at universe and report level.

So where i will implement this sql script .

Please explain.

[nscheaffer · Accepted solution]

I see two possibilities.

1. You put everything except "SELECT" in the SELECT field of a new dimension like this...

You would replace "GETDATE()" with your column from your table.

2. You could also create a derived table in the data foundation of your universe with the conversion applied to the column you are interested in.

Noel

[Former Member · Accepted solution]

thanks Noel

it work.