first of the 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  ???

[Former Member · Accepted solution]

Just a little modif to Dan code:

declare @val datetime

declare @ret datetime

declare @var1 nvarchar(6)

select @val = '09/24/2013'

select @var1 = case

                            when datepart(qq,@val)=1 then '01/01/'

                            when datepart(qq,@val)=2 then '04/01/'

                            when datepart(qq,@val)=3 then '07/01/'

                            when datepart(qq,@val)=4 then '10/01/'

                       end

select @ret = @var1 + cast(year(@val) as nvarchar(4))

select @ret

go

[markmumy · Accepted solution]

Well, let's just make is super simple.  One line, all done inline, no case statement.  

(3*datepart(qq,a)) + 1 ) - 3 ||'/01/'||year(a)  -- 'a' is a column in my table per below

begin

declare @val datetime;

declare @ret datetime;

declare @var1 nvarchar(6);

create table #x ( a date );

insert into #x values ( '12/24/2013' );

insert into #x values ( '11/24/2013' );

insert into #x values ( '10/24/2013' );

insert into #x values ( '09/24/2013' );

insert into #x values ( '08/24/2013' );

insert into #x values ( '07/24/2013' );

insert into #x values ( '06/24/2013' );

insert into #x values ( '05/24/2013' );

insert into #x values ( '04/24/2013' );

insert into #x values ( '03/24/2013' );

insert into #x values ( '02/24/2013' );

insert into #x values ( '01/24/2013' );

select a, convert(varchar(20),( (3*datepart(qq,a)) + 1 ) - 3 ||'/01/'||year(a) ) from #x;

end

go

[Former Member · Accepted solution]

Brilliant, can we simplyfied the convert as follow?

select a, convert(varchar(20),(3*datepart(qq,a)) - 2 ||'/01/'||year(a) ) from #x;

[former_member203645 · Accepted solution]

I tried in my Business Objects universe with below and it says error

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"

[markmumy · Accepted solution]

Can you try using double pipes as string concatenators?

[Former Member · Accepted solution]

Or, cast(Year(RBD.FACTSALES_V.PAYROLLPAIDDATE) as nvarchar(4))

[Former Member · Accepted solution]

Hi RUC,

Are you still hitting the same error after trying Mark and Uvernes' suggestions ?

Thanks
Andrew

[dan_thrall]

The datepart function receives a qq function which will return the quarter number for a given date.  From there, you can sample the date via case statement to turn quarter numbers into start dates.

Here's an example of doing so

1> declare @val datetime

2> select @val = "09/24/2013"

3> declare @ret datetime

4> select @ret = case when datepart(qq,@val) = 1 then "01/01/2013"

5> when datepart(qq,@val)=2 then "04/01/2013"

6> when datepart(qq,@val)=3 then "07/01/2013"

7> when datepart(qq,@val)=4 then "10/01/2013"

8> end

9> select @ret

10> go

@ret

----------------------------------

      Jul  1 2013 12:00:00.000000AM

(1 row affected)

Dan