Have you ever been using SAP BusinessObjects WebIntelligence and wanted to turn a prompt from a date time into a date?
I do a lot of demos using Microsoft SQL Server and by default Microsoft SQL Server saves everything as a date & time. So what if I don’t want the time? Simply convert the datetime into a date within the semantic layer.
Ignoring the time 12:00:00
There are many solutions, including many which rely on some form of string parsing, but why make it more complicated than it has to be? This is what I like to use:
CONVERT(DATETIME, CONVERT(INT, GETDATE()))
This is the perfect solution if all the data is stored as 1/1/2010 12:00:00, wherein the time element is 0.
Ignoring time and rounding down
If indeed there is a time and it’s important to round down, e.g. 1/1/2010 11:59pm should be rounded down to 1/1/2010, then in this case I use:
CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE())))
This is great when accessing call center data and I need to group calls around a specific day, but the time is still extremely relevant.
Anyone else have any commonly used tips or tricks within the semantic layer? Post it in the comments below!
«Good BI»
Great tip! Thanks!
I know there are lots of different ways to do this one, but I prefer to use the datepart function: http://msdn.microsoft.com/en-us/library/ms174420.aspx – this may be slightly less processor intensive as it’s only extracting a portion of the data rather than converting it.
Hello Mr. Taylor i follow your blog for some time now and really it is very good. I have a question about time zone conversion issue.
As you know Business Objects Auditor database captures data in GMT, i need to convert that to PST.
On the universe level i have created a object
new_time( tablename.column, ‘GMT’, ‘PST’ )
This works fine on the Universe but i want to be able to create a variable at the report level too. So do you know how to have a variable at the universe that would keep the same GMT time format but convert it to PST time.
For eg. Oct 10, 2010 3:00:00pm in GMT as Oct 10, 2010 8:00:00Am in PST.