[SalesForce] Old style JOIN (ANSI JOINs) syntax is not allowed. Split DATE

I'm using a simple SQL query to get DAY, MONTH, YEAR out of a Date field (BIRTH_DATE) in Automation Studio (of marketing cloud).

SELECT 
  CUST_CODE
, EXCLUDED_FROM_NPS
, BIRTH_DATE
, DATEPART(DD,BIRTH_DATE) AS DAY_DOB
, DATEPART(MM,BIRTH_DATE) AS MONTH_DOB
, DATEPART(YYYY,BIRTH_DATE) AS YEAR_DOB
FROM Import_Customer_TH

When I validate the syntax, it gives the error

An error occurred while checking the query syntax. Errors: Old style
JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax.
_NPS, is not a known data extension or system data view. You can only query existing data extensions or system data views

I don't have any Joins in the query.
I tried this version too, but same error

 SELECT 
  CUST_CODE
, EXCLUDED_FROM_NPS
, BIRTH_DATE
, DAY(BIRTH_DATE) AS DAY_DOB
, MONTH(BIRTH_DATE) AS MONTH_DOB
, YEAR(BIRTH_DATE) AS YEAR_DOB
FROM Import_Customer_TH

Best Answer

The SQL parser in SFMC is really dumb sometimes.

I'd recommend something like what @Bo Hu suggested, but just add brackets around it:

SELECT 
  CUST_CODE
, [EXCLUDED_FROM_NPS]
, BIRTH_DATE
, DATEPART(DD,BIRTH_DATE) AS DAY_DOB
, DATEPART(MM,BIRTH_DATE) AS MONTH_DOB
, DATEPART(YYYY,BIRTH_DATE) AS YEAR_DOB
FROM Import_Customer_TH

I did a quick test and this appears to work.

Related Topic