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.
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.
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.
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
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
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…
And I ALWAYS format dates as yyyy-mm-dd, when it will be used electronically. Even in files names.