Transform Date to Number while keeping the value

[#1]

Hey community

I am having the following issue:

I would like to reload data from a table within SAP. Since I only need the latest 3 months I applied this formula:

date(AddMonths(MonthStart(today()),-2),'YYYYMMDD') which would result by today in 20140801.

The problem is, if I put that into the where statement, it wouldn't work. If i put the plain numbers - hard coded - into it, it works. I think the solution would be, to transform the date format shown above, into a number. Unfortunatley, if I do that, it turns to 41852.

How would you guys go about such an issue?

regards

Sebastian

[alexandros17]

All dates have a corresponding numerical value as you experienced, if you need the date again simply do

Date(41852

...

let me know

[rubenmarin]

Hi Sebastian, if the hard-coded number works you can try with the $-expansion formula:

$(=date(AddMonths(MonthStart(today()),-2),'YYYYMMDD'))

[anbu1984]

Let vDt= date(AddMonths(MonthStart(today()),-2),'YYYYMMDD');

Sql Select * from Table where DateField > '$vDt';

[#5]

Hey all,

I tried both, Ruben's and Anbu's proposal, unfortunatley it didn't work. Especially Anbu's solution seemed promising, since the LET statement should display the content of a variable as it is?

[anbu1984]

Can you post sample qvw

[MarcoWedel]

Text(date(AddMonths(MonthStart(today()),-2),'YYYYMMDD'))

or

Num#(Text(date(AddMonths(MonthStart(today()),-2),'YYYYMMDD')))

?

[rubenmarin · Accepted solution]

Following anbu's solution, if vDt has the value you want, maybe it work using () in where clause:

Let vDt= date(AddMonths(MonthStart(today()),-2),'YYYYMMDD');

Sql Select * from Table where DateField > '$(vDt)';

[#9]

This one worked. The brackets were missing. I think though, that Marco's solution might work too. Going to try that...

Thanks guys!!