Removing Time from Date in Calculated Field

I am trying to show some quoting metrics in a dashboard. The BAQ I created uses calculated fields to show sums per months. I am trying to show this graphically, but I am having issue with showing the months correctly in order.

I was trying to use a calculated field to show the year and month along the x-axis, but Epicor puts them in the wrong order because it is a string.

image

I tried to convert that field to a date format, but when I do it also adds the time 12:00:00AM at the end. This makes the month at the front get cut off on the chart, making the whole thing un-readable.

Anyone have a solution for this?

In your calculated field (for the string), add a 0 in front of the single digit months. There are various ways to do that.

Here’s how I do the month only, but you can add the year in there. (there are more elegant ways to do this)

(case when datepart(month,OrderDtl.RequestDate)<10 then convert(varchar, '0') + convert(varchar, datepart(month,OrderDtl.RequestDate)) else convert(varchar, datepart(month,OrderDtl.RequestDate)) end)

I have made my own year month code to do this. I padded the month to two digits with a case statement.

There may be a more elegant way to do this though.

Brad

Try this in the calculated field

CAST ([Database Field] AS DATE), this will remove time

Scott

1 Like