Is there a Reason one can not use DATE_PART_STR and CONCAT?

SQL++
1

I am trying to build a String in my N1QL Query which also has a Date Field and i am trying to extract the Year portion. The intresting part is when i use the DATE_PART_STR without trying to concat it works fine. As soon as i try to CONCAT with other fields it returns NULL

SELECT r.Record.OriginatingSystem.OriginatingSystemID || "/" || DATE_PART_STR(r.Record.Dates.OriginalEntryTimestamp,"year") as `Path`,
       r.Record.ListingKeyNumeric
FROM rets r
WHERE r._type ='Residential'
    AND r.Record.ListingKeyNumeric = "362837563"
2

DATE_PART_STR() return type is number and concat expects string.
You can convert number to string by explicit cast TO_STRING(1234) and use CONCAT.

You can also use SUBSTR(r.Record.Dates.OriginalEntryTimestamp,0,4) this gives year as string of date ISO-8601

SELECT r.Record.OriginatingSystem.OriginatingSystemID || "/" || TO_STRING(DATE_PART_STR(r.Record.Dates.OriginalEntryTimestamp,"year")) as `Path`,
       r.Record.ListingKeyNumeric
FROM rets r
WHERE r._type ='Residential'
    AND r.Record.ListingKeyNumeric = "362837563"

OR

  SELECT CONCAT(r.Record.OriginatingSystem.OriginatingSystemID, "/",  
    SUBSTR(r.Record.Dates.OriginalEntryTimestamp,0,4)) as `Path`,
           r.Record.ListingKeyNumeric
    FROM rets r
    WHERE r._type ='Residential'
        AND r.Record.ListingKeyNumeric = "362837563"