Creating a Universe condition to check for date being in the current month

[Former Member]

I have a field in one of my Universe tables, (SF_Opportunity) called CloseDate. The field has a datetime definition and the data displays as 10/16/2009 12:00:00 AM. I want to create a condition on the SF_Opportunity table in my Universe which will allow me to easily query (in WebI) only those SF_Opportunity records which have a CloseDate within the current month.

I'm very new to Universe Designer and I've found no documentation which explains how to accomplish this.

Could anyone provide an example of how I do this?

Many thanks...

[Former Member · Accepted solution]

Alan,

I copied your SQL code into the "where" section of Universe Condition window, but it didn't parse. Knowing very little about SQL, I couldn't troubleshoot it. At this stage, I'd be happy with an example of how to create a condition which selects only records having a CloseDate more than 60 days from today.

Thanks...

[Former Member · Accepted solution]

Hi David,

Sorry about the quality of the solution, as I said I do not have SQL Server on this side so I am going to struggle with the exact syntax.

=@Select('YourClass\CloseDate') >= DateAdd(Day, -60, GetDate())

and if that does not do it try

=@Select('YourClass\CloseDate') >= DateAdd('Day', -60, GetDate())

This uses the location of your CloseDate object.

Let me know how you get on.

Regards

Alan

[Former Member · Accepted solution]

Hi David,

A simpler approach for the month would be:

dbo.SF_Opportunity.CloseDate BETWEEN 
(CONVERT(datetime, CONVERT(varchar(6), GetDate(), 112)+'01', 112))
AND 
(DATEADD(m,1,CONVERT(datetime, CONVERT(varchar(6), GetDate(), 112)+'01', 112))-1)

The second line conters the date to teh format YYYYMMDD (thats the 112), then truncates the DD and adds 01 instead before converting it back

The secind claise uses this and the dateadd function to add a month, then subtracts a day.

You can test these by putting the individual clauses universe objects and using them in queries.

For instance create an object StartOfMonth and set the code to

CONVERT(datetime, CONVERT(varchar(6), GetDate(), 112)+'01', 112))

Note: parsing this in the universe will fail as there is no table associated with the query. However, if you use it in conjunction with another object it will be fine.

Regards

Alan

[Former Member]

Alan,

dbo.SF_Opportunity.CloseDate BETWEEN

(CONVERT(datetime, CONVERT(varchar(6), GetDate(), 112)+'01', 112))

AND

(DATEADD(m,1,CONVERT(datetime, CONVERT(varchar(6), GetDate(), 112)+'01', 112))-1)

worked like a champ!

Thank you so much...

[Former Member]

That great news

Cheers

Alan

[Former Member]

The miracle of Google. Using getdate() did the trick and using this as a condition for selecting only those records with a close date 60 or more days out worked:

dbo.SF_Opportunity.CloseDate >= (getdate()+60)

Crisis 1 solved. Now I'll start building upon this to figure out when a record has a CloseDate within the current month.

Thanks for the responses...

[Former Member]

I forgot to mention that I'm using SQL Server. It didn't like the Oracle syntax,

[Former Member]

Try this

=[CloseDate] 
BETWEEN 
Convert(datetime, 
'01/'+
CONVERT(varchar(2), DatePart(mm, GetDate()))+
'/'+
CONVERT(varchar(4), DatePart(yyyy, GetDate()))
,103)
AND
DateAdd(d,-1,DateAdd(m, 1, Convert(datetime, 
'01/'+
CONVERT(varchar(2), DatePart(mm, GetDate()))+
'/'+
CONVERT(varchar(4), DatePart(yyyy, GetDate()))
,103)))

You will proably have to play around a bit as I have not got SQL Server access here

Regards

Alan

Edited by: Alan McClean on Jun 11, 2009 4:21 PM

[Former Member]

Try

=[CloseDate] BETWEEN TO_DATE('01/'||TO_CHAR(SYSDATE, 'MM/YYYY')), 'DD/MM/YYYY') AND LAST_DAY(SYSDATE)

The should work in ORACLE

Regards

Alan