first date of the any quarter

[former_member203645]

Hi all,

I am looking for a solution in Sybase IQ for my BO reporting point.

Based on the date I should calculate First of the quarter. Example : I have a start date and end date, If my end date is today 09/24/2013 then my start date should be 07/01/2013.

Please provide how to get the first day of the quarter based on the dates  ???

Environment : 4.0 SP5, IDT

[Former Member · Accepted solution]

Hi RUC,

Can you try below logic:

In universe designer create a date filter as:

date =

to_date(CASE WHEN QUARTER(your_date)=1 THEN {fn concat('01/01',YEAR(your_date)}WHEN QUARTER(your_date)=2 THEN {fn concat('04/01',YEAR(your_date)}WHEN QUARTER(your_date)=1 THEN {fn concat('07/01',YEAR(your_date)}

WHEN QUARTER(your_date)=1 THEN {fn concat('10/01',YEAR(your_date)}

END),'mm/dd/yyyy')

Gaurav

[former_member203645 · Accepted solution]

I tried in Sybase IQ and I see the below :

CASE WHEN QUARTER(RBD.FACTSALES_V.PAYROLLPAIDDATE)=1 THEN   '01/01/' + Year(RBD.FACTSALES_V.PAYROLLPAIDDATE)

         WHEN QUARTER(RBD.FACTSALES_V.PAYROLLPAIDDATE)=2 THEN   '04/01/' + Year(RBD.FACTSALES_V.PAYROLLPAIDDATE)

         WHEN QUARTER(RBD.FACTSALES_V.PAYROLLPAIDDATE)=3 THEN   '07/01/' + Year(RBD.FACTSALES_V.PAYROLLPAIDDATE) 

         WHEN QUARTER(RBD.FACTSALES_V.PAYROLLPAIDDATE)=4 THEN   '10/01/' + Year(RBD.FACTSALES_V.PAYROLLPAIDDATE) 

END

Data exception - data type conversion is not possible. First operand to + ( ADD) operator must be a valid arithmetic expression

SQL Code = -1009145, ODBC 3 State = "HY000"

[nscheaffer · Accepted solution]

Perhaps you need to convert the year to a string so concatenates the strings rather than trying to add them.

Noel

[former_member203645 · Accepted solution]

Thanks it worked

[Former Member]

Use

dateadd(qq,datediff(qq,0,table.column),0)

[Former Member]

Hi,

Go through below link once its very much helpful

http://blog.davidg.com.au/2012/11/workdays-between-two-dates-in-webi.html

Regards,

Giri

[nscheaffer]

It looks like this question has been cross-posted and already answered here.

Noel