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

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (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  Next
 
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
JPetlev
Forum Enthusiast
Forum Enthusiast



Joined: 01 Nov 2006

Posts: 1097



PostPosted: Mon Jan 14, 2013 5:06 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

sydneyisle12 wrote:
Okay, the offset suggestion made me think. Technically, U.S. Central time is UTC-6:00. So, if I just use straight GMT instead of the Central time offset, that would be only half an hour away from what I'm looking for. Close enough for jazz, most likely, since no one is doing anything at 5:30am around here. Does anyone know if that would work, or do I just take a shot in the dark and let everybody know? icon_smile.gif


Are you asking if you can use GMT as the server's timezone... sure that will get you CLOSE... but I don't compare my data integrity to horseshoes or hand grenades. Close isn't going to cut it on my servers.
Unless there's a hard business reason as to why you cannot wrap the data, why would you want to compromise the quality?

Quote:
...since no one is doing anything at 5:30am around here..

I cannot tell you the number of times thinking like that has come to cause me grief. Whether it's a timezone, rounding to decimal specific decimal point, or even forgetting a person's middle initial, the best rule is "Always assume someone will need the data!"
Imagine what would happen if the company you work for suddenly decided to move the servers to NY (EST), or started to provide data 24/7 for some reason.. icon_redface.gif

It sounds like it's your database to control (no pesky DBA's to worry about) so it's ultimately up to you, but if you're asking for our opinions.. I'd fix it right the first time and never have to worry about it again.


Here's a great example that actually happened in my current company Someone decided way back when, during the initial building of one of our employement payroll systems, to not use a unique row identifier (aka key) on an employee related header table. They figured "First and Last" name should be enough for most things, and if not we can suppliment it by using the full address columns.

Well, we happened to have employed two brothers. Both with the same First name and Last name (No suffix or Middle initial in table), both lived at home with the same address. Both did not have a cell phone so both used the same land line... We had no way to identify the two individuals as unique.. and so one didn't get paid properly for a few weeks when they started until we realized the cause icon_sad.gif Eventually we added both Middle Initial and Name Suffix (Jr vs III) to seperate out the brothers.
Back to top
sydneyisle12
Forum Member
Forum Member



Joined: 10 Jan 2013

Posts: 7



PostPosted: Mon Jan 14, 2013 5:14 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

Oh, how I wish it were my database to control. That would make this sooo much easier! But alas, no, there's a DBA team to deal with, and as wonderful as they are, there are limits to what can be done.

Normally I would agree with you that the data needs to be as close to perfect as possible... but the more I dig into this problem, the more ridiculous it seems. banghead.gif

What I want to end up with is something to the effect of "SELECT <insert fields here> FROM <insert tables and joins here> WHERE <insert date/time field here> BETWEEN <yesterday at 5:30am> AND <today at 5:30am>" but that doesn't seem to be at all realistic.

The users don't want to have to set parameters every time they run the report. They want it to auto-generate data for the previous business day's time frame. Go figure.
Back to top
JPetlev
Forum Enthusiast
Forum Enthusiast



Joined: 01 Nov 2006

Posts: 1097



PostPosted: Mon Jan 14, 2013 5:18 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

You have access to modify the UNIVERSE correct?

If so you can do exactly what you want by using this blog post:
Using Magic Dates in Prompts
Just be sure that your date calculations in the prompts account for the 30 min.

It's actually rather easy to do in the universe, I use them all the time.

Now if you cannot change the database, nor the universe... my suggestion is to push back to whomever is requesting this and tell them THATS where it needs to be fixed icon_smile.gif

Good luck.

NOTE: Moderator might want to break this out into it's own thread, as we're moving away from the original sticky post at this point.
Back to top
sydneyisle12
Forum Member
Forum Member



Joined: 10 Jan 2013

Posts: 7



PostPosted: Wed Feb 20, 2013 10:57 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

Here's what I finally got to work for Previous Business Day:

Code:
@Select(Bill_Datetime) BETWEEN dateadd(day, datediff(day,0,getdate())-1, '04:30:00') AND dateadd(day, datediff(day,0,getdate()), '04:30:00')


Thank you for all the help! Now I have another quandry. Current Business Day needs to be from 4:30am today to Now. I must have tried 30 different ways to get this one to work (including at least four variations on the code snippet above) and came up short every time. I'm out of ideas... any suggestions?

Thanks in advance,
Sydney
Back to top
JPetlev
Forum Enthusiast
Forum Enthusiast



Joined: 01 Nov 2006

Posts: 1097



PostPosted: Wed Feb 20, 2013 11:13 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

sydneyisle12 wrote:
Here's what I finally got to work for Previous Business Day:

Code:
@Select(Bill_Datetime) BETWEEN dateadd(day, datediff(day,0,getdate())-1, '04:30:00') AND dateadd(day, datediff(day,0,getdate()), '04:30:00')


Thank you for all the help! Now I have another quandry. Current Business Day needs to be from 4:30am today to Now. I must have tried 30 different ways to get this one to work (including at least four variations on the code snippet above) and came up short every time. I'm out of ideas... any suggestions?

Thanks in advance,
Sydney


Umm would this work ?
Code:
@Select(Bill_Datetime) BETWEEN dateadd(day, datediff(day,0,getdate())  , '04:30:00') AND dateadd(day, datediff(day,0,getdate())+1, '04:30:00')

Same code as you had before, but looking 1 day 'future' from 4:30 this morning? You might want to change the 2nd half to simply "GetDate()" if your system can pre-post billing information and you only want data up to the second that the report is run instead of the current business day.
Back to top
sydneyisle12
Forum Member
Forum Member



Joined: 10 Jan 2013

Posts: 7



PostPosted: Wed Feb 20, 2013 11:32 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

Well, that result is better than some I've gotten, in the sense that it actually generates data. Trouble is, the numbers are way below what they should be, which has puzzled me from the beginning. The formulas I try that actually produce results produce bill counts that are way lower than they should be.

I've asked our DBAs if this could be a database related issue, but since we have a direct query in a separate report that produces accurate numbers from the same tables, there's gotta be something wrong in the structure of the universe or my objects, but I can't find it to save my life.

UPDATE: Well, as it turns out, the other direct query does not point to the same database after all, and the one this universe is built on doesn't get synced with live data, so current business day numbers will never be accurate. Fabulous. icon_smile.gif
Back to top
udamico
Forum Member
Forum Member



Joined: 06 Jun 2013
ASUG Icon
Posts: 2



PostPosted: Mon Jun 10, 2013 6:37 am 
Post subject: Re: SQL Server Relative Dates

hello

i am looking for the relative date to get the previous weekday for oracle.

report running tuesday to friday can get sysdate-1, but when it runs on monday, i need it to get me fridays date.

i dont care about holidays, just weekdays vs weekends.
Back to top
Mark P
Forum Devotee
Forum Devotee



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


flag
PostPosted: Mon Jun 10, 2013 6:41 am 
Post subject: Re: SQL Server Relative Dates

udamico wrote:
hello

i am looking for the relative date to get the previous weekday for oracle.

report running tuesday to friday can get sysdate-1, but when it runs on monday, i need it to get me fridays date.

i dont care about holidays, just weekdays vs weekends.


Code:
case when to_char(sysdate,'D') in (1,2,7) then next_day(sysdate-7,'Friday') else sysdate-1 end

_________________
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
udamico
Forum Member
Forum Member



Joined: 06 Jun 2013
ASUG Icon
Posts: 2



PostPosted: Mon Jun 10, 2013 7:19 am 
Post subject: Re: SQL Server Relative Dates

Mark P wrote:


Code:
case when to_char(sysdate,'D') in (1,2,7) then next_day(sysdate-7,'Friday') else sysdate-1 end




i modified it a little bit to include trunc since it was required for my needs.

works well (for last friday)
icon_smile.gif

Code:
case when to_char(sysdate,'D') in (1,2,7) then next_day(trunc(sysdate)-7,'Friday') else trunc(sysdate)-1 end
Back to top
chiclothe
Forum Member
Forum Member



Joined: 02 Sep 2013

Posts: 1



PostPosted: Mon Sep 02, 2013 9:24 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

I think these would make good stickys?
Back to top
Mark P
Forum Devotee
Forum Devotee



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


flag
PostPosted: Tue Sep 03, 2013 3:44 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

chiclothe wrote:
I think these would make good stickys?


It already is. icon_wink.gif

_________________
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
marc527
Forum Member
Forum Member



Joined: 08 Sep 2005
ASUG Icon
Posts: 29


flag
PostPosted: Thu Dec 05, 2013 4:52 pm 
Post subject: RDBMS-specific Relative Dates Netezza

Netezza Relative Dates


Today
Code:
current_date


Yesterday
Code:
current_date - 1


Start of Current Month
Code:
add_months(current_date,0) - date_part('day', current_date)+1


End of Current Month
Code:
add_months(date_trunc('month',current_date),1)-1


Start of Previous Month
Code:
add_months(current_date,-1) - date_part('day', add_months(current_date,-1))+1


End of Previous month
Code:
add_months(current_date,0) - date_part('day', current_date)


Start of Current Quarter
Code:
date_trunc('quarter',current_date)


End of Current Quarter
Code:
add_months(date_trunc('quarter',current_date),3)-1


Start of Previous Quarter
Code:
date_trunc('quarter',current_date)


End of Previous Quarter
Code:
date_trunc('quarter',current_date)-1


Start of Current Year
Code:
date_trunc('year',current_date)


End of Current Year
Code:
add_months(date_trunc('year',current_date),12)-1


Start of Previous Year
Code:
add_months(date_trunc('year',current_date),-12)


End of Previous Year
Code:
date_trunc('year',current_date)-1


Start of 2nd Half of Current Year
Code:
add_months(date_trunc('year',current_date),6)


Current Date of Previous Year
Code:
add_months(current_date, -12)


Start of Current Month of Previous Year

Code:
add_months(date_trunc('month',current_date),-12)


Hope it helps,
Marc
Back to top
keamo
Forum Member
Forum Member



Joined: 22 Feb 2013

Posts: 7
Location: Dallas



PostPosted: Wed Jan 08, 2014 4:27 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

Op some of these are wrong, check in sqlmgr studio before hand.
_________________
BI Consultant who produces music icon_wink.gif

Music2work2:
www.soundcloud.com/keamo
FacebookPage:
https://www.facebook.com/pages/Keamo/152961801429211‎
Back to top
Mark P
Forum Devotee
Forum Devotee



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


flag
PostPosted: Thu Jan 09, 2014 4:51 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server, Oracle and Net

keamo wrote:
Op some of these are wrong, check in sqlmgr studio before hand.

Which ones are wrong?

They were all checked in the relevant SQL tool at the time of addition.

_________________
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
p.mangal
Forum Member
Forum Member



Joined: 29 Jan 2014

Posts: 12



PostPosted: Tue Apr 15, 2014 2:14 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

Can you please do that for Teradata as well?
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 2 of 3 All times are GMT - 5 Hours
Goto page Previous  1, 2, 3  Next
 
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.0744 seconds using 17 queries. (SQL 0.0402 Parse 0.0010 Other 0.0333)
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