Getting Publish Date From For All Documents Published in Content tree

PABITRA KUMAR PRADHAN asked on January 27, 2016 13:11

Code snippet to get Publish Date From For All Documents Published in Content tree. Code Snippet means through C# Code i have to get publish Date of all documents.

getting publish date from

Correct Answer

Joshua Adams answered on January 27, 2016 18:57

Something like this can work, depends on the version of Kentico being used as well. This should work for 8 and above I believe. Inside the foreach statement is where you can build your string or do whatever you need to do.

DataSet dsDocs = DocumentHelper.GetDocuments().Where("DocumentPublishFrom >" + DateTime.Now).And().Where("DocumentPublishTo <" + DateTime.Now);
        if(!DataHelper.DataSourceIsEmpty(dsDocs))
        {
            foreach(DataRow row in dsDocs.Tables[0].Rows)
            {
                //build string 
            }
        }

0 votesVote for this answer Unmark Correct answer

Recent Answers

Bryan Soltis answered on January 27, 2016 15:06

I'm not exactly sure what you mean by code snippet, however, you can easily see this information in the built-in reports: Reporting / All Reports / Page reports / Content inventory report.

If you look at the General Tab / Tables / Inventory Table / Edit , you can see the SQL query to get the data. From this, you could create a custom macro or function to get the values you are looking for.

SELECT NodeAliasPath AS 'Alias Path', 
    DocumentCulture AS 'Language', 
    DocumentName AS 'Name', 
    DocumentModifiedWhen AS 'Last modified', 
    UserName AS 'Last modified by',
    StepDisplayName AS 'Workflow step',
    DocumentPublishFrom AS 'Publish from',
    DocumentPublishTo AS 'Publish to'
FROM View_CMS_Tree_Joined
LEFT JOIN CMS_User ON DocumentModifiedByUserID = UserID
LEFT JOIN CMS_WorkFlowStep ON DocumentWorkflowStepID = StepID
WHERE (@OnlyPublished = 0 OR ([DocumentCanBePublished] = @OnlyPublished AND ([DocumentPublishFrom] IS NULL OR [DocumentPublishFrom] <= @CMSContextCurrentTime) AND ([DocumentPublishTo] IS NULL OR [DocumentPublishTo] >= @CMSContextCurrentTime))) 
AND (NodeSiteID = @CMSContextCurrentSiteID)
AND (@ModifiedFrom IS NULL OR DocumentModifiedWhen >= @ModifiedFrom)
AND (@ModifiedTo IS NULL OR DocumentModifiedWhen < @ModifiedTo) 
AND (NodeAliasPath LIKE @path)
AND (@Language IS NULL OR @Language = '-1' OR DocumentCulture = @Language)
AND (@name IS NULL OR DocumentName LIKE '%'+@name+'%')
ORDER BY NodeAliasPath

1 votesVote for this answer Mark as a Correct answer

PABITRA KUMAR PRADHAN answered on January 28, 2016 06:14

Hi Joshua Adams ,

Thanks for providing code snippet but i am getting below error while execution can you guide error where exact problem occurs .

[DataConnection.HandleError]: Query: SELECT * FROM View_CMS_Tree_Joined AS V WITH (NOLOCK, NOEXPAND) LEFT OUTER JOIN COM_SKU AS S WITH (NOLOCK) ON V.NodeSKUID = S.SKUID WHERE (DocumentPublishFrom >1/28/2016 10:24:10 AM) AND (DocumentPublishTo <1/28/2016 10:24:10 AM) AND [DocumentCulture] = @DocumentCulture Caused exception: Incorrect syntax near '10'.

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on January 28, 2016 16:55

Your date has to be wrapped in the ' tags. Try adding them to the where clause like this:
"DocumentPublishFrom > '" + DateTime.Now + "'"
"DocumentPublishTo < '" + DateTime.Now + "'"

0 votesVote for this answer Mark as a Correct answer