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 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
Mark P
Forum Devotee
Forum Devotee



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


flag
PostPosted: Fri Mar 26, 2010 6:43 am 
Post subject: DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here's the same set of dates in SQL Server.
Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8.

Yesterday
Code:
cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime)

Today
Code:
cast(convert(char(10),getdate(),23) as datetime)

The following also works for today:
Code:
dateadd(dd, datediff(dd,0,getdate()), 0)

Start of Current Month
Code:
cast(convert(char(7),getdate(),23)+'-01' as datetime)

End of Current Month
Code:
dateadd(d,-1,dateadd(m,1,cast(convert(char(7),getdate(),23)+'-01' as datetime)))

Start of Previous Month
Code:
dateadd(m,-1,cast(convert(char(7),getdate(),23)+'-01' as datetime))

End of Previous Month
Code:
dateadd(d,-1,cast(convert(char(7),getdate(),23)+'-01' as datetime))

Start of Current Quarter
Code:
dateadd(qq,datediff(qq,0,getdate()),0)

End of Current Quarter
Code:
dateadd(qq,datediff(qq,-1,getdate()),-1)

Start of Previous Quarter
Code:
dateadd(qq,datediff(qq,2,getdate()),0)

End of Previous Quarter
Code:
dateadd(qq,datediff(qq,0,getdate()),-1)

Start of Current Year
Code:
dateadd(yy, datediff(yy,0,getdate()), 0)

End of Current Year
Code:
dateadd(yy, datediff(yy,-1,getdate()), -1)

Start of Previous Year
Code:
dateadd(yyyy,-1,dateadd(yy, datediff(yy,0,getdate()), 0))

End of Previous Year
Code:
dateadd(yy, datediff(yy,1,getdate()), -1)


Again, note that this is simply manipulation of the sysdate functionality within SQL Server and should in no way detract from the importance of a calendar table. It should assist it.

Hope it helps,
Mark

_________________
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!


Last edited by Mark P on Fri Apr 09, 2010 10:01 am, edited 2 times in total
This post has been reported for Other. The current status is Closed / Resolved.
Moderator Nick Daniels closed this report Click for Details
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13981
Location: London


flag
PostPosted: Fri Mar 26, 2010 6:47 am 
Post subject: Re: SQL Server Relative Dates

Mark,

I think these would make good stickys?

_________________
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: 11431
Location: Manchester, UK


flag
PostPosted: Fri Mar 26, 2010 6:55 am 
Post subject: Re: SQL Server Relative Dates

Oracle Relative Dates

Yesterday:
Code:
trunc(sydate)-1

Today:
Code:
trunc(sysdate)

Start of Current Month:
Code:
trunc(sysdate,'mm')

End of Current Month:
Code:
last_day(sysdate)

Start of Previous Month:
Code:
trunc((trunc(sysdate,'mm')-1),'mm')

End of Previous Month:
Code:
trunc(sysdate,'mm')-1

Start of Current Quarter:
Code:
trunc(sysdate,'q')

End of Current Quarter:
Code:
add_months(trunc(sysdate,'q'),3)-1

Start of Previous Quarter:
Code:
trunc(trunc(sysdate,'q')-1,'q')

End of Previous Quarter:
Code:
trunc(sysdate,'q')-1

Start of Current Year:
Code:
trunc(sysdate,'y')

End of Current Year:
Code:
add_months(trunc(sysdate,'y'),12)-1

Start of Previous Year:
Code:
trunc(trunc(sysdate,'y')-1,'y')

End of Previous Year:
Code:
trunc(sysdate,'y')-1


Please note that this is simply manipulation of the sysdate functionality within Oracle and should in no way detract from the importance of a calendar table. It should assist it.

Hope it helps,
Mark

_________________
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!


Last edited by Mark P on Fri Mar 26, 2010 9:28 am, edited 1 time in total
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Fri Mar 26, 2010 8:59 am 
Post subject: Re: SQL Server Relative Dates

How about collecting the Oracle stuff (and any other database too) and make this a "Relative Dates" rather than SQL Server only?
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13981
Location: London


flag
PostPosted: Fri Mar 26, 2010 9:04 am 
Post subject: Re: SQL Server Relative Dates

Good Plan Dave icon_cool.gif .

One other thing that should have sticky status, IMO, is Reporter blokes date checklist.
The amount of people I have lead to that:-
http://www.forumtopics.com/busobj/viewtopic.php?t=84923&postdays=0&postorder=asc&highlight=format+checklist+xir2&start=15


Cheers,

Mark.

_________________
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."


Last edited by Mak 1 on Fri Mar 26, 2010 9:07 am, edited 1 time in total
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002

speaker.gif*16
Posts: 22138
Location: Dallas, Texas


flag
PostPosted: Fri Mar 26, 2010 9:05 am 
Post subject: Re: SQL Server Relative Dates

So make it a "Dates" sticky instead of "Relative Dates", that's fine too.
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2019-09-19 Stephen Few Blog Post on Multivariate Visualization
• 2019-02-11 Update on Query Banding
• 2018-10-19 BI Evolution
Back to top
Mak 1
Forum Aficionado
Forum Aficionado



Joined: 06 Jan 2005

Posts: 13981
Location: London


flag
PostPosted: Fri Mar 26, 2010 9:12 am 
Post subject: Re: SQL Server Relative Dates

Dave,

Thats sort of up to Mark.
I slightly butted in, I doubt he was very surprised, he knows me fairly well, because we answer so many sysdate type questions.

The trouble with Reporter Bloes checklist is it transverses different areas. The great thing is it is the nest consolidation I've seen.

I'm sort of campaigning for more stickies, as I feel some of the best info can be lost, unless you really know how to search for it icon_cool.gif .

I'm not saying we should have too many, as that would defeat the object icon_wink.gif. One other I could write up would be config of the Java panel run time parameters and the benefits of changing these, for example.

Cheers,

Mark.

_________________
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."


Last edited by Mak 1 on Fri Mar 26, 2010 10:06 am, edited 1 time in total
Back to top
Mark P
Forum Devotee
Forum Devotee



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


flag
PostPosted: Fri Mar 26, 2010 9:33 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

Dave,

I've copied the Oracle info over and changed the topic title.

Regards,
Mark

_________________
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
zaif235
Forum Associate
Forum Associate



Joined: 14 Jun 2010

Posts: 757
Location: United States


flag
PostPosted: Tue Sep 14, 2010 4:49 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

If we have MTD, QTD and YTD functions too it would be good.

Thanks,
Zaif
Back to top
Mark P
Forum Devotee
Forum Devotee



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


flag
PostPosted: Fri Oct 01, 2010 8:51 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

zaif235 wrote:
If we have MTD, QTD and YTD functions too it would be good.

Thanks,
Zaif


That's a whole different topic Zaif.

Although a related one, certainly worth of its own topic.

_________________
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
Mark P
Forum Devotee
Forum Devotee



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


flag
PostPosted: Mon Oct 22, 2012 7:48 am 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

Thought I'd do my good deed for the day and add the Netezza equivalents. I'm quite the Netezza noob so it maybe that there are more performant versions of these. Please feel free to reply and I'll correct accordingly. icon_smile.gif

Yesterday:
Code:
CURRENT_DATE-1

Today:
Code:
CURRENT_DATE

Start of Current Month
Code:
CURRENT_DATE+1-EXTRACT("day" FROM CURRENT_DATE)

End of Current Month
Code:
add_months(CURRENT_DATE+1-EXTRACT("day" FROM CURRENT_DATE),1)-1

Start of Previous Month
Code:
add_months(CURRENT_DATE+1-EXTRACT("day" FROM CURRENT_DATE),-1)

End of Previous Month
Code:
CURRENT_DATE-EXTRACT("day" FROM CURRENT_DATE)

Start Of Current Year
Code:
cast(EXTRACT("year" FROM CURRENT_DATE)||'-01-01' as date)

End Of Current Year
Code:
add_months(cast(EXTRACT("year" FROM CURRENT_DATE)||'-01-01' as date),12)-1

Start Of Previous Year
Code:
add_months(cast(EXTRACT("year" FROM CURRENT_DATE)||'-01-01' as date),-12)

End Of Previous Year
Code:
cast(EXTRACT("year" FROM CURRENT_DATE)||'-01-01' as date)-1

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



Joined: 10 Jan 2013

Posts: 7



PostPosted: Thu Jan 10, 2013 4:50 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

Hi there! Total newbie to this forum, so please be kind. seeya.gif

If I need to start a new thread for this, please let me know and I'll do so, but it is closely related, so figured I would start here. My company has a "business day" which runs from 5:30am to 5:30am (Central time) instead of midnight to midnight, and much of the work is done in the overnight hours, so naturally, standard midnight to midnight dates don't pull accurate data for a report that wants, say, the previous day's productivity stats. Can anybody give me a suggestion for the best way to tweak some of these date range formulas for SQL Server so they reflect a non-standard business day accurately?

Thanks in advance,
Sydney
Back to top
kbrazell
Principal Member
Principal Member



Joined: 19 Aug 2003

Posts: 184
Location: DFW Metroplex (but mobile) I applied to Mars One


flag
PostPosted: Thu Jan 10, 2013 5:13 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

sydneyisle12 wrote:
Hi there! Total newbie to this forum, so please be kind. seeya.gif

If I need to start a new thread for this, please let me know and I'll do so, but it is closely related, so figured I would start here. My company has a "business day" which runs from 5:30am to 5:30am (Central time) instead of midnight to midnight, and much of the work is done in the overnight hours, so naturally, standard midnight to midnight dates don't pull accurate data for a report that wants, say, the previous day's productivity stats. Can anybody give me a suggestion for the best way to tweak some of these date range formulas for SQL Server so they reflect a non-standard business day accurately?

Thanks in advance,
Sydney


There may be some date math tricks you could apply, but I'd try a fake timezone first. The :30 may make that not work well, but if you could set up a server in a timezone that was the right number of hours different and then run reports from it - and the timezone translation would take care of it for you.

This is a link for a +5:30 TZ: http://en.wikipedia.org/wiki/UTC%2B05:30

I cannot find a -5:30 timezone. Not sure which shift direction you need (-18:30 or +5:30)

I started here: http://en.wikipedia.org/wiki/Time_zone

_________________
Kyle Brazell
BOBJ XIr3
Oracle ... 10g, 11g
on HP-UX, AIX, Linux

Web Developer
Oracle ... 11g
on Linux (via OSX)

IoT Developer
Embedded System Programmer
Back to top
JPetlev
Forum Enthusiast
Forum Enthusiast



Joined: 01 Nov 2006

Posts: 1097



PostPosted: Thu Jan 10, 2013 6:07 pm 
Post subject: Re: RDBMS-specific Relative Dates SQL Server and Oracle

sydneyisle12 wrote:
Can anybody give me a suggestion for the best way to tweak some of these date range formulas for SQL Server so they reflect a non-standard business day accurately?



It's actually quite easy, here's a few samples:
Code:
select '1-Today Start' , DateAdd(minute,330,cast(convert(char(10),getdate(),23) as datetime))
UNION
select '2-Yesterday Start' , DateAdd(minute,330,cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime))
UNION
select '3-Prior Day Start' , DateAdd(minute,330,cast(convert(char(10),dateadd(d,-2,getdate()),23) as datetime))
UNION
select '5-Prior Month Start' , DateAdd(minute,330,dateadd(m,-1,cast(convert(char(7),getdate(),23)+'-01' as datetime)))

Will return 330 minutes AFTER the start of midnight the day before, thus 5:30am.
You can use a DateAdd wrapper around all your dates to change the time to be whatever you need. Just calculate as the original post mentiones for any date/time, then use DateAdd to adjust by minutes as needed.
Back to top
sydneyisle12
Forum Member
Forum Member



Joined: 10 Jan 2013

Posts: 7



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

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
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 3 All times are GMT - 5 Hours
Goto page 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.0947 seconds using 17 queries. (SQL 0.0020 Parse 0.0601 Other 0.0326)
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