Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
];
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
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
thank you! it works great!!