Date Conversion Made Easy…

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»

3 replies on “Date Conversion Made Easy…”

  1. 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.

Comments are closed.