Display as month

pilio.lee · October 30, 2023, 7:30am

Hello everyone,

I would like to convert date representation to month representation in BAQ.
ex) 2023.10.23 >> 10 2023.09.18 >> 9
I used the Month function, but I got the error popup below.
What could be the problem?
Thanks in advance!

iarunkumark · October 30, 2023, 8:26am

Hi,

Datepart should work, Try changing date to Datetime.else try with month(Date)

Thanks,
Arun

aarong · October 30, 2023, 9:08am

Could you not just use the month() function?

carlosqt · October 30, 2023, 9:52am

Hi, here some samples using DATEPART.

select 
	[OrderHed].[Company] as [OrderHed_Company],
	[OrderHed].[OrderNum] as [OrderHed_OrderNum],
	[OrderHed].[OrderDate] as [OrderHed_OrderDate],
	(CAST(DATEPART(MM, OrderHed.OrderDate) AS CHAR(2))) as [Calculated_OrdDtMonth],
	(datepart(month, OrderHed.OrderDate)) as [Calculated_OrdDtMonth2],
	(CASE WHEN DATEPART(MM, OrderHed.OrderDate) > 9 THEN  CAST(DATEPART(MM, OrderHed.OrderDate) AS CHAR(2)) ELSE '0' + CAST(DATEPART(MM, OrderHed.OrderDate) AS CHAR(2)) END) as [Calculated_OrdDtMonth3],
	(DATEPART(MM, OrderHed.OrderDate)) as [Calculated_OrdDtMonth4],
	(CASE DATEPART(MM, OrderHed.OrderDate)
 WHEN 1 THEN 'January'
 WHEN 2 THEN 'February'
 WHEN 3 THEN 'March'
 WHEN 4 THEN 'April'
 WHEN 5 THEN 'May'
 WHEN 6 THEN 'June'
 WHEN 7 THEN 'July'
 WHEN 8 THEN 'August'
 WHEN 9 THEN 'September'
 WHEN 10 THEN 'October'
 WHEN 11 THEN 'November'
 WHEN 12 THEN 'December'
 ELSE ''
 END) as [Calculated_OrdMonthStr],
	(DATEPART(DD, OrderHed.OrderDate)) as [Calculated_OrdDtDay],
	(DATEPART(YYYY, OrderHed.OrderDate)) as [Calculated_OrdDtYEar]
from Erp.OrderHed as OrderHed

pilio.lee · October 30, 2023, 11:09pm

I tried that, but I got the same result.

aarong · October 30, 2023, 11:13pm

Does any of the other calculated fields use dateadd?

CASE
    WHEN POHeader.TermsCode = 'N30' THEN DATEADD(month, 1, PODetail.DueDate)
    WHEN POHeader.TermsCode = 'N15' THEN DATEADD(day, 15, PODetail.DueDate)
    WHEN POHeader.TermsCode = 'N45' THEN DATEADD(day, 45, PODetail.DueDate)
    WHEN POHeader.TermsCode = 'N60' THEN DATEADD(month, 2, PODetail.DueDate)
    WHEN POHeader.TermsCode = 'N90' THEN DATEADD(month, 3, PODetail.DueDate)
    WHEN POHeader.TermsCode = 'PPD' THEN PODetail.DueDate  -- No change
    ELSE PODetail.DueDate  -- Default to no change
END

pilio.lee · October 30, 2023, 11:23pm

Yes. I used Dateadd on EstDate. (as you can see in the screenshot above).

aarong · October 30, 2023, 11:25pm

Try above.

pilio.lee · October 30, 2023, 11:37pm

I tried what you suggested, but the same problem still occurs.

pilio.lee · October 31, 2023, 12:25am

I just got rid of all DATEADD. I had no choice but to use EOMONTH (although the exact date will not appear).
But now MONTH appears well. I think maybe DATEADD and datepart don’t work together.
Thank you to everyone who helped.

gpayne · October 31, 2023, 12:30am

To do something this I added a join to Purchase Terms and just use the actual due days.

dateadd(day, PurTerms.NumberOfDays, PORel.DueDate )