If I understand correctly, for each Product, you have one or more Document records, but only the latest document should be considered active. I'm assuming you're trying to join these two tables to get a list of all Product records and the currently active Document for each Product.

Here's a couple of examples of how to do that, one using a JOIN and one using an APPLY statement.

CREATE TABLE Product (
    Product_code numeric(7, 0) NOT NULL IDENTITY(1,1) Primary key,
    Product nvarchar(30) NOT NULL,
    StartDate datetime NOT NULL,
    EndDate datetime NULL,
    DatePrice datetime NOT NULL,
    ListPrice money NOT NULL
);

INSERT INTO Product(Product, StartDate, EndDate, DatePrice, ListPrice)
VALUES ('Transparent Window Pane', CAST('20170101' AS DATETIME), CAST('20190101' AS DATETIME), CAST('20171203' AS DATETIME), 520)
    , ('Translucent Window Pane', CAST('20180101' AS DATETIME), CAST('20180129' AS DATETIME), CAST('20180129' AS DATETIME), 1500)
    , ('Tinted Window Pane', '20170102', CAST('20170901' AS DATETIME), CAST('20170102' AS DATETIME), 700)
    
CREATE TABLE Document (
    DocumentID uniqueidentifier NOT NULL DEFAULT(NEWID()) PRIMARY KEY,
    [Document] varbinary(MAX) NOT NULL,
    Doc_code numeric(6, 0) NOT NULL,
    Date_document datetime NOT NULL,
    Product_code numeric(7,0)
);

ALTER TABLE Document ADD CONSTRAINT FK_Document_Product FOREIGN KEY (Product_code) REFERENCES Product(Product_code);
CREATE NONCLUSTERED INDEX IX_NC_Document_DateDocument_ProductCode ON Document(Date_document, Product_code ) INCLUDE (DocumentID);

INSERT INTO Document(
    Document
    , Doc_code
    , Date_document
    , Product_code
)
VALUES (CAST('Transparent windows are amazing!' AS VARBINARY(MAX)), 1, CAST('20170101' AS DATETIME), 1)
    , (CAST('Item removed due to complaints regarding bird-strikes.' AS VARBINARY(MAX)), 1, CAST('20170802' AS DATETIME), 1)
    , (CAST('Translucent windows are reliable.' AS VARBINARY(MAX)), 1, CAST('20170910' AS DATETIME), 2)
    , (CAST('Tinted windows improve privacy.' AS VARBINARY(MAX)), 1, CAST('20180101' AS DATETIME), 3)
    , (CAST('Tinted windows are cost-effective.' AS VARBINARY(MAX)), 1, CAST('20180121' AS DATETIME), 3)
    , (CAST('Tinted windows reduce harmful UV radiation.' AS VARBINARY(MAX)), 1, CAST('20180131' AS DATETIME), 3)

SELECT Product.*
FROM Product

SELECT document.*
FROM Document

-- Get each Product, and the Documents for it, excluding any Documents where the product has one or more valid documents later than the current Document's date.
-- LEFT JOIN to include Product records that do not have a related document.
SELECT Product.Product_code
    , document.DocumentID
FROM Product
    LEFT JOIN Document
        ON document.Product_code = Product.Product_code
            AND document.Date_document <= GETDATE()
WHERE NOT EXISTS (
    SELECT 1
    FROM Document AS DocumentLater
    WHERE DocumentLater.Product_code = Product.Product_code
        AND DocumentLater.Date_document <= GETDATE()
        AND DocumentLater.Date_document > document.Date_document
)

-- Get all Products, and get only the most recent Document (ignoring any with a future Date_document).
-- OUTER APPLY to include products that do not have a document.
-- Seems to be more efficient than the LEFT JOIN, and returns only one record for each product where two Document records have the same Date_document.
SELECT Product.Product_code
    , LATEST_document.DocumentID
FROM Product
    OUTER APPLY (
        SELECT TOP 1 document.*
        FROM Document
        WHERE document.Product_code = Product.Product_code
            AND document.Date_document <= GETDATE()
        ORDER BY document.Date_document DESC
    ) AS LATEST_DOCUMENT