Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to make 00:00 hours to 24:00

Answered How to make 00:00 hours to 24:00

  • Friday, February 08, 2013 4:24 AM
     
     
    Sign In to Vote
    0

    Hi, I have created dashboard from power pivot, in my scenario i need to plot time in axis fields.

    I have done as follow.

    But requirement I have to keep 00:00 hour as a 24:00,

    Is there any possibility do like this.

    thanks in advance



     

All Replies

  • Saturday, February 09, 2013 6:06 PM
     
     
    Sign In to Vote
    0

    You could do this in a calculated column with an IF expression to check if that hour is 00 and if so substitute 24.

    I'd suggest it would be cleaner and simpler to do this with a separate Time table (dimension).  Then you can label your hours exactly how you need them, and even have multiple versions (buckets) to handle different user requirements.  Here's a link to a post I wrote with an example script you could start from.

    I would ask though, would events that occur at 00:05 (12:05 AM) fall in the "24:00" bucket? 

    And would that bucket get sorted to the end (ie, after 23:00) or still at the beginning (before 01:00)?

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



     
  • Monday, February 11, 2013 4:09 AM
     
     
    Sign In to Vote
    0

    many thanks.. Brent Greenwood

    actually I am using 1hour interval.In excel starts with 00:00 to 23:00

    My requirement demand me to Start with 01:00 to 24:00.
    I could not find any solution in excel.

    If we make replace with 00:00 to 24:00. It's became 1st value of the day.

    Actually I need to start reading interval from 01:00 to  ends with 24:00.
    is there any solution start time interval From 01:00 to ends with 24:00.

    thanks in advance

     
  • Monday, February 11, 2013 5:14 AM
     
     Answered Has Code
    Sign In to Vote
    0

    I take it you're not up for the Time dimension route.  A simple approach would be to create a calculated column that uses the HOUR function against your timestamp field to get the hour bucket.  And then wrap that in an IF statement to substitute 24 for 0 like this:

    =IF(HOUR([EventTime]) = 0, 24, HOUR([EventTime]))

    Then you could simply put that hour on your axis.  The colon and zero minutes aren't actually doing anything, so you could just keep it simple and leave those out and use the hour by itself.

    Let me know if that works for you.

    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com