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:
I did a quick test and this appears to work.