Skip to Content
author's profile photo Former Member
Former Member

Date Formulas :

Hi,

I have a SQL query to make the report dynamically determine date -last day of previous month -SQL Server

select convert(char(10),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),121)

select convert(char(10),dateadd(yyyy, -1, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))), 121)

In the crytal report at presnet we are using the user prompt- the user enters the value manully.

in [{?CurrentPeriod}, {?PriorPeriod}]

On the database the date field is of type 'yyyy-mm-dd' .....need guidance as to whatbtype of formaula should the crystal report should use so that we can make it dynamically pick the date for the last day previous month and last day previous month ( of previous year)......

Please advice.

Thanks,

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Apr 19, 2011 at 09:06 PM

    Here's how I do it...

    1) Create a "DateRange" parameter.

    - Set the type to numeric

    - Value = 1 / Description = Current Period

    - Value = 2 / Description = Prior Period

    In the SQL I use something like this...

    DECLARE @DateRange INT, @BEG DateTime, @END DateTime
    
    SET @DateRange = {?DateRange}; -- 1 = Current Period / 2 = Prior Period
    
    IF @DateRange = 1 SET @BEG = DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0)
              ELSE SET @BEG = DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1,0);
    
    IF @DateRange = 1 SET @END = DATEADD(ms,-5,DATEADD(mm, DATEDIFF(mm,0,GETDATE())+1, 0))
              ELSE SET @END = DATEADD(ms,-5,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0));
    

    In this example, SQL Server will set @BEG = 1st day of the current month and @END = last day of the current month if the {?DateRange} parameter = 1

    ...ELSE it will set @BEG to the 1st day of last month and @End to the last day of last month.

    So to tie it into a full SQL Command, you can use it like this...

    DECLARE @DateRange INT, @BEG DateTime, @END DateTime
    
    SET @DateRange = {?DateRange}; -- 1 = Current Period / 2 = Prior Period
    
    IF @DateRange = 1 SET @BEG = DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0)
              ELSE SET @BEG = DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1,0);
    
    IF @DateRange = 1 SET @END = DATEADD(ms,-5,DATEADD(mm, DATEDIFF(mm,0,GETDATE())+1, 0))
              ELSE SET @END = DATEADD(ms,-5,DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0));
    
    SELECT 
    Column1,
    Column2,
    Column3,
    Column4
    FROM TableName
    WHERE DateField BETWEEN @BEG AND @END
    

    HTH,

    Jason

    Fixed an error in SQL code

    Edited by: Jason Long on Apr 19, 2011 4:08 PM

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.