Search code examples
visual-studio-2012dynamics-crm-2011

How can i modify DATEDIFF to find results in the past?

I have created a Visual Studio report (database is dynamics crm 2011). This report shows me from now (today) all elements 13 months in the past.

DATEDIFF(Month, CRMAF_FilteredQuote.createdon, CURRENT_TIMESTAMP) <=13;

How should I modify this statement to get the results from the final day of the previous month? For example all documents from the 30th September through to 1st August?

Thank you very much for your ideas.

Peter

Solution

  • You can get last day of previous month using below given query

    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

    To find all documents created between two dates (last day of previous month and today), use below given query

    SELECT * FROM Entity_Name WHERE CreatedOn Between DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AND GETDATE()