Year-Month in BAQ calculator

I am trying to display year-month as 2018-07 in a BAQ calculated field. The following only displays the year:

Year(LaborHed.ClockInDate) + ‘-’ + Month(LaborHed.ClockInDate)

What gives?

Thanks in advance.

It it displaying the year? Or the month added to the year? Ex clock-in date of 2-2-2016, is it displaying 2016 or 2018 (2016+2).

It’s probably treating them as integers and needs to be casted to characters.

image

Actually upon 2nd glance, it’s not making sense at all. The 2nd column YM is the one where I have the calculation and it’s not even showing the correct year.
image

it’s adding them, not concatenating them. Cast them as Nvarchar before you do the concatenation.

Got it with leading zeros for months 1 thru 9:

Cast(Year(LaborHed.ClockInDate) As Nvarchar(4)) + ‘-’ + RIGHT(‘0’ + Cast(Month(LaborHed.ClockInDate) As Nvarchar(2)),2)

Wouldn’t LaborHed.ClockInDate.ToString("yyyy-MM")

do the trick?

(assuming the calc field type is nvchar)

EDIT: The above is incorrect. See the next two posts

Isn’t that C#? Baq is SQL

1 Like

Yes. (total brain fart)

But the following would work in a BAQ

LEFT(CONVERT(varchar, JobHead.ClosedDate, 23),7)

Format 23 is yyyy-MM-dd

2 Likes

Does MM preserve the leading 0’s? That’s needed for sorting purposes when sorting numbers set up as NVARCHARS, (otherwise 22 comes before 3 etc.)

edit, looks like it does! Nice.

Apparently so…

image

And I ALWAYS format dates as yyyy-mm-dd, when it will be used electronically. Even in files names.

1 Like