0

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
  • Have you tried rename EXCLUDED_FROM_NPS column? the error looks like it confused Excluded_From_NPS with TSQL "From _NPS". – Bo Hu Dec 6 '17 at 4:46
  • ahhhh!! interesting – sfdcFanBoy Dec 6 '17 at 15:12
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.