Date Formulas :

[Former Member]

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,

[Former Member · Accepted solution]

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