BusinessObjects Board

based on date need a quarter currnt month in sql server

building semantic layer
#1

hi,

I have a Date field i wanted to provide current month and quarter filters

it should based on current date when ever uesr hit the report filter should caliculate from that date to last 3 months for quarter and simlarly for month.

for oracle i have this
Quarter :
date between add_months(trunc(sysdate,‘q’),-3) and add_months(add_months(trunc(sysdate,‘q’),-3),3)-0.0002/24)

but sql servrer ??

current month : Oracle
date between (trunc(sysdate,‘MM’)) and trunc(sysdate)
but sql servrer ??

select month(GETDATE)
select Datepart(qq,GETDATE) with this i am getting just number

thanks

pavan_au143 (BOB member since 2007-07-25)

#2

This post should help you:-

https://bobj-board.org/t/152613

Mak 1 :uk: (BOB member since 2005-01-06)

#3

This will give you quarter start date:

DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)

This will give you quarter end date:

DATEADD(qq,DATEDIFF(qq,0,GETDATE())+1,0)-1

Create a filter condition as below:

YourTable.date between  DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0) and DATEADD(qq,DATEDIFF(qq,0,GETDATE())+1,0)-1

BO_Chief :us: (BOB member since 2004-06-06)