Selecting todays date as an @variable

[funkmonsteruk]

I have a report which is currently user generated. Basically the user enters a date (have checked SQL, it is listed as @variable('Date')) and the report runs figures based on this date.

However i am going to run the report as an overnight scheduled task on my PC and i need it to automatically generate figures baseed on the current date.

Could somebody tell me how to do this (i'm guessing it will involve a new line of SQL or some VBA code.

 

[blom0344]

Hello Funkm..

Don't know which RDBMS you are working on, but in case of ORACLE you could extract the date part out of SYSDATE, like:

To_Char(SYSDATE,'MM/DD/YY')

where sysdate is the timestamp of the operating system.

So, modify SQL to something were you compare the dates from database (get date parts as well) to the above expression.

Modify SQL in query panel and activate the "Do not generate SQL" option. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[funkmonsteruk]

Thanx for your help, ended up replacing the @variable('date') with CURRENT DATE AND IT SEEMS TO WORK FINE

 

[promero]

> Modify SQL in query panel and activate the "Do not
> generate SQL" option

Modifying the SQL is not a good idea. It makes maitenance complex. And adding objects to the query removes the "Do not generate SQL" option, without asking first.

Instead of that, you can create an object <Today> in the universe with the date expression (CURRENT DATE). Designer reports bad syntax, but it's ok. Then, in the query panel of report, compare the field object with <Today>.

We use DB2, and i made a lot of objects based on CURRENT DATE. We put them under a class named &quot;Date expressions&quot;:
[ul][li]<Today> = [tt]CURRENT DATE[/tt][/li]
[li]<Yesterday> = [tt]CURRENT DATE - 1 DAY[/tt][/li]
[li]<First day of this month> = [tt]CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS[/tt][/li]
[li]<Last day of this month> = [tt]CURRENT DATE + 1 MONTH - DAY(CURRENT DATE) DAYS[/tt][/li]
[li]<First day of this year> = [tt]CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS - (MONTH(CURRENT DATE) - 1) MONTHS[/tt][/li]
[li]<Last monday> = [tt]DATE(DAYS(CURRENT DATE)/7*7)[/tt][/li][/ul]and so on

 

[blom0344]

Hello Promero,

> Modify SQL in query panel and activate the &quot;Do not
> generate SQL&quot; option.

You are right about the maintenance issue. I used the &quot;do no generate SQL&quot; option when adding optimizer hints to SQL's directed at ORACLE source.
Your solution is the cleaner one .............
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[funkmonsteruk]

This is really useful Promero, thanks a lot.

What coding would i use for
a) a month ago
b) 3 months ago

Funkmonster

 

[promero]

<A month ago> = [tt]CURRENT DATE - 1 MONTH[/tt]
<3 months ago> = [tt]CURRENT DATE - 3 MONTHS[/tt]

The definition of a month is ambiguous. Not all months have the same number of days. The easy cases are days 1-28: AMONTHAGO('2002-04-20')='2002-03-20'. But what about AMONTHAGO('2002-03-30')? DB2 solves it returning '2002-02-28'.

Sorry for my errata, but the correct expression to <Last monday> is = DATE((DAYS(CURRENT DATE)-1)/7*7+1). It's based in the fact that 0001-01-01 was monday. Have a look to the definitions of DAYS() and DATE() in the DB2 SQL reference. A/7*7 (integer division) is equivalent to A - (A mod 7) (module is not supported in DB2 v5).