Set Analysis rounding numbers - Remove Milliseconds during Script load

[#1]

hello,

I've been looking for a solution to remove milliseconds during a load script.

something like this in SQL Server:

DATEADD(MILLISECOND, DATEPART(ms, dateValue1)*-1, dateValue1) TruncatedDate

I saw a post to truncate seconds using the floor, but I couldn't figure it out to remove milliseconds.

basically I am trying to solve a problem where Set Analysis is rounding numbers, or dates if you like.

on my attached example: I have a particular number of 40693.999998611 that translates to this date: '2011-05-30 23:59:59.880'

with a very simple Set Analysis QV is picking that record and it shouldn't

=SUM(

          {

                    $<

                              StartDate = {">=40694<40695"}

                    >

          }

          Amount

          )

this will produce: 1500

StaticData:

          NOCONCATENATE

          LOAD * INLINE

          [          StartDate, Amount

                    40693.999998611, 1000

                    40693, 10

                    40694, 500

                    40695,200

          ];

[swuehl · Accepted solution]

I think you could truncate the milliseconds like this:

SET TimeFormat='h:mm:ss TT';

StaticData:

    NOCONCATENATE

    LOAD *,

    floor(StartDate)+Time#(Time(StartDate)) as StartDate2

    INLINE

    [    StartDate, Amount       

        40693.999998611, 1000

        40693, 10

        40694, 500

        40695,200

    ];

StartDate2 should only hold time up to the seconds now. And in your example, the sum seems to be correct and will return 500.

Hope this helps,

Stefan

[#3]

thank you! it works great!!