CUIC Date/Time Filter

[justine.joubran]

Hello,

When I create a new report in CUIC, I am using "anonymous block" and setting parameters like :start_date and :end_date

Those parameters appear in the Filter and need to be manually filled

From Date : mm/dd/yyyy hh:mm:ss 

End Date: mm/dd/yyyy hh:mm:ss

Is there a way to replace this filter by the "Relative Date and Time Range" which is found in WebView?

Any advice is appreciated.

Thanks,

Justine.

[BTciscoN00b]

I am trying this and having an issue with the last month section (case 6).

The begin date is working as 12/01/2013

but the end date is calculating as 12/31/2012

I  am thinking that is because it is looking at January 01 to try to find  the end of the month of Dec. It must be subtracting it twice somehow.

Has anyone else run into this?

[halblas]

Hi Brandy,

After fixing ‘This Month’ for December, I had another issue running ‘Last Month’ in January.

The latest changes I made are;

WHEN 1 THEN CONVERT(DATETIME,(CONVERT(Varchar(15),GETDATE(),112)))

WHEN 2 THEN CONVERT(DATETIME,(CONVERT(Varchar(15),DATEADD(d, -1,GETDATE()),112)))

WHEN 3 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())-2),CONVERT(CHAR,GETDATE(),112))

WHEN 4 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())+5),CONVERT(CHAR,GETDATE(),112))

WHEN 5 THEN Convert(Varchar(2), DATEPART(m,GETDATE())) + '/01/' + Convert(Char(4), DATEPART(YY,GETDATE()))

WHEN 6 THEN Convert(Varchar(2), DATEPART(m,DATEADD(mm,-1,GETDATE()))) + '/01/' + Convert(Char(4), DATEPART(YY,DATEADD(MM,-1,GETDATE())))

WHEN 7 THEN '01/01/' + Convert(Char(4), DATEPART(yy,GETDATE()))

WHEN 8 THEN '01/01/' + Convert(Char(4), DATEPART(yy,DATEADD(yy,-1,GETDATE())))

ELSE @AbsBegin

END

SELECT @EndDate = CASE @RelativeDate

WHEN 1 THEN CONVERT(Varchar(30), GETDATE(),112) + ' 23:59:59'

WHEN 2 THEN CONVERT(Varchar(30), DATEADD(d, -1,GETDATE()),112) + ' 23:59:59'

WHEN 3 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())-8),CONVERT(CHAR,GETDATE(),112))

WHEN 4 THEN DATEADD(dd,- (DATEPART(dw,GETDATE())-1),CONVERT(CHAR,GETDATE(),112)) + '23:59:00'

WHEN 5 THEN DATEADD(d,-1,CONVERT(Varchar(30),DATEPART(m,DATEADD(mm,1,GETDATE()))) + '/01/' + CONVERT(Char(4),DATEPART(YY,DATEADD(d,1,GETDATE())))) + ' 23:59:59'

WHEN 6 THEN DATEADD(d,-1, CONVERT(Varchar(30), DATEPART(m,GETDATE())) + '/01/' + CONVERT(Char(4), DATEPART(YY,DATEADD(d,1, GETDATE())))) + ' 23:59:59'

WHEN 7 THEN '12/31/' + CONVERT(Char(4), DATEPART(yy,GETDATE()))+ ' 23:59:59'

WHEN 8 THEN '12/31/' + CONVERT(Char(4), DATEPART(yy,DATEADD(yy,-1,GETDATE())))+ ' 23:59:59'

Hope this helps,

Hans

[BTciscoN00b]

Thanks!