BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: No events within the next 45 days.

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2
9 members found this topic helpful
Goto page Previous  1, 2, 3
 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11432
Location: Manchester, UK


flag
PostPosted: Wed Apr 16, 2014 3:38 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

p.mangal wrote:
Can you please do that for Teradata as well?


I don't have access to Teradata - if someone does and can provide a list of them then feel free.

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13981
Location: London


flag
PostPosted: Wed Apr 16, 2014 4:25 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

p.mangal wrote:
Can you please do that for Teradata as well?

http://www.forumtopics.com/busobj/viewtopic.php?t=162166

_________________
Next Available for Contract Work - January 2018

Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
Debbie
Forum Addict
Forum Addict



Joined: 01 Mar 2005

Posts: 2778
Location: Cheshire, England


flag
PostPosted: Thu Apr 24, 2014 9:47 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

So in the interests of saving other people's sanity, here are some relative dates that work in multi-source oracle/SQL Server BI4 IDT:

(Start of) first day of current month:
Code:
cast(timestampadd('SQL_TSI_DAY',-dayofmonth(now())+1,now()) as date)

(Start of) first day of last month:
Code:
cast(timestampadd('SQL_TSI_MONTH',-1,timestampadd('SQL_TSI_DAY',-dayofmonth(now())+1,now())) as date)

(End of) last day of last month:
Code:
timestampadd('SQL_TSI_SECOND',-1,cast(cast(timestampadd('SQL_TSI_DAY',-dayofmonth(now())+1,now()) as date) as timestamp))

(Start of) first day of current year:
Code:
cast(timestampadd('SQL_TSI_DAY',-dayofmonth(timestampadd('SQL_TSI_MONTH',-month(now())+1,now()))+1,timestampadd('SQL_TSI_MONTH',-month(now())+1,now())) as date)

(Start of) first day of last year:
Code:
timestampadd('SQL_TSI_YEAR',-1,cast(cast(timestampadd('SQL_TSI_DAY',-dayofmonth(timestampadd('SQL_TSI_MONTH',-month(now())+1,now()))+1,timestampadd('SQL_TSI_MONTH',-month(now())+1,now())) as date) as timestamp))

(End of) last day of last year:
Code:
timestampadd('SQL_TSI_SECOND',-1,cast(cast(timestampadd('SQL_TSI_DAY',-dayofmonth(timestampadd('SQL_TSI_MONTH',-month(now())+1,now()))+1,timestampadd('SQL_TSI_MONTH',-month(now())+1,now())) as date) as timestamp))

(Start of) n days ago:
Code:
cast(timestampadd('SQL_TSI_DAY',-n,now()) as date)

(Start of) n months ago:
Code:
cast(timestampadd('SQL_TSI_DAY',-dayofmonth(timestampadd('SQL_TSI_MONTH',-n,now()))+1,timestampadd('SQL_TSI_MONTH',-n,now())) as date)


If there are better ways of doing this, please tell me! Otherwise I'll add others as I figure them out...

Debbie

ETA: all mostly cobbled together from here: http://www.smallsql.de/doc/sql-functions/date-time/index.html


Last edited by Debbie on Wed Aug 24, 2016 4:08 am, edited 1 time in total
Back to top
NycPriya
Forum Associate
Forum Associate



Joined: 04 Nov 2010

Posts: 529



PostPosted: Wed Sep 17, 2014 4:45 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

Hey Debbie,

I am fighting to make some date objects so that i can filter my quries. Would these sql codes that you posted work with sql server 2012 based universe as well? Thanks
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13981
Location: London


flag
PostPosted: Thu Sep 18, 2014 6:49 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

Quote:
am fighting to make some date objects so that i can filter my quries. Would these sql codes that you posted work with sql server 2012 based universe as well? Thanks


Quote:
multi-source oracle/SQL Server BI4 IDT:


Multi source universe only.

_________________
Next Available for Contract Work - January 2018

Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11432
Location: Manchester, UK


flag
PostPosted: Thu Sep 18, 2014 10:53 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

NycPriya wrote:
Hey Debbie,

I am fighting to make some date objects so that i can filter my quries. Would these sql codes that you posted work with sql server 2012 based universe as well? Thanks


Use the original set of SQL Server based date objects at the start of the thread.

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
NycPriya
Forum Associate
Forum Associate



Joined: 04 Nov 2010

Posts: 529



PostPosted: Fri Sep 19, 2014 10:03 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

These are the ones i built before asking debbie.
Code:
DATEADD(year,DATEDIFF(year,0,GETDATE()),0) AS "First Day Of Year",
DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)-1 AS "Last Day Of Year",
DATEADD(YY,DATEDIFF(YY,0,getdate()),0) AS "First Day Of Year(1)",
CONVERT (date, GETDATE()) AS "Current Date",
CONVERT (date, GETDATE()-1) AS "Yesterday",
DATEADD(MM,DATEDIFF(MM,0,getdate()),0) AS "First Day Of Current Month",
DATEADD(MM,DATEDIFF(MM,0,getdate())+1,0)-1 AS "Last Day Of Current Month",
DATEADD(WW,DATEDIFF(WW,0,getdate())+1,0)-1 AS "Last Day Of Current Week",
DATEADD(WW,DATEDIFF(WW,0,getdate()),0) AS "First Day Of Current Week"


They definitely did not seem to work in my queries. Had the same type of objects in my previous engagements in 3.1 on oracle with oracle syntax and it worked fine.
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13981
Location: London


flag
PostPosted: Mon Oct 06, 2014 5:22 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

Try here for Terradata:-
http://www.forumtopics.com/busobj/viewtopic.php?t=162166

_________________
Next Available for Contract Work - January 2018

Business Objects Versions 4,5,6, XI R2, 3.2, 4.1 SP3
Oracle, SQL Server, Sybase, Netezza, SAP BW.

Red Adair: "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur."
Back to top
Debbie
Forum Addict
Forum Addict



Joined: 01 Mar 2005

Posts: 2778
Location: Cheshire, England


flag
PostPosted: Thu Oct 09, 2014 6:31 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

NycPriya wrote:
They definitely did not seem to work in my queries.


Can you be more specific? What error messages on which objects? And is this a multi-source IDT universe or a single-source universe?

Debbie

ETA: Ah, you said 3.1, so presumably single-source.....
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11432
Location: Manchester, UK


flag
PostPosted: Tue Dec 09, 2014 7:08 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

NycPriya wrote:
These are the ones i built before asking debbie.
Code:
DATEADD(year,DATEDIFF(year,0,GETDATE()),0) AS "First Day Of Year",
DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)-1 AS "Last Day Of Year",
DATEADD(YY,DATEDIFF(YY,0,getdate()),0) AS "First Day Of Year(1)",
CONVERT (date, GETDATE()) AS "Current Date",
CONVERT (date, GETDATE()-1) AS "Yesterday",
DATEADD(MM,DATEDIFF(MM,0,getdate()),0) AS "First Day Of Current Month",
DATEADD(MM,DATEDIFF(MM,0,getdate())+1,0)-1 AS "Last Day Of Current Month",
DATEADD(WW,DATEDIFF(WW,0,getdate())+1,0)-1 AS "Last Day Of Current Week",
DATEADD(WW,DATEDIFF(WW,0,getdate()),0) AS "First Day Of Current Week"


They definitely did not seem to work in my queries. Had the same type of objects in my previous engagements in 3.1 on oracle with oracle syntax and it worked fine.


If you're just trying to parse them, they won't work.

While Oracle has the concept of SELECT SYSDATE FROM DUAL SQL Server is simply SELECT GETDATE(). As such, the object won't parse because it isn't associated with a table. Don't associate it with a table but when you use it with any table-based object, it will work.

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
brandonwilhelm
Forum Member
Forum Member



Joined: 23 Mar 2015

Posts: 1



PostPosted: Mon Mar 23, 2015 12:02 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

Hi Mark,

Do you have a random function for BusinessObjects?

I have a report that brings back about 900 work order numbers a week, and I have a need to select only a random 100 of them.

Can you help with that?
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 19316
Location: Bratislava


flag
PostPosted: Tue Mar 24, 2015 2:31 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

Welcome to Bicon_mrgreen.gifB!
brandonwilhelm wrote:
Do you have a random function for BusinessObjects?

I have a report that brings back about 900 work order numbers a week, and I have a need to select only a random 100 of them.

This old topic can be helpful:
http://www.forumtopics.com/busobj/viewtopic.php?t=142676

_________________
BO: BI 4.0, 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win, Linux and AIX servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
charlie
Forum Associate
Forum Associate



Joined: 20 Aug 2002

Posts: 898
Location: Woodinville, WA USA


flag
PostPosted: Fri Aug 19, 2016 3:10 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

Debbie wrote:
So in the interests of saving other people's sanity, here are some relative dates that work in multi-source oracle/SQL Server BI4 IDT:If there are better ways of doing this, please tell me! Otherwise I'll add others as I figure them out...

Debbie


Debbie, this is great stuff! If you don't mind my asking, how did you figure them out? I have SQL Server Mgt Studio, and they don't parse, so I can't test them outside of the IDT.

Edit: Ah, I saw your post in this thread. I stumbled on that site earlier today after much banghead.gif.

_________________
Regards,

Chaz
Back to top
Debbie
Forum Addict
Forum Addict



Joined: 01 Mar 2005

Posts: 2778
Location: Cheshire, England


flag
PostPosted: Wed Aug 24, 2016 4:07 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

I'll go back and add the link to my original post for future reference ....

debbie
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 19316
Location: Bratislava


flag
PostPosted: Wed Jan 18, 2017 3:58 am 
Post subject: Re: DB-specific Relative Dates SQL Server, Oracle, Netezza,

And at last, something for DB2

DB2 Relative Dates

Code:
SELECT
       current date AS today,
       current date - 1 day AS yesterday,
       current date - (day(current date)-1) day AS beginning_of_current_month,
       current date - (day(current date)-1) day - 1 months as beginning_of_previous_month,
       current date - (day(current date)) day AS end_of_previous_month
  FROM sysibm.sysdummy1
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 3 of 3 All times are GMT - 5 Hours
Goto page Previous  1, 2, 3
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.1152 seconds using 17 queries. (SQL 0.0036 Parse 0.0769 Other 0.0347)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo