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

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

Today

cast(convert(char(10),getdate(),23) as datetime)

The following also works for today:

dateadd(dd, datediff(dd,0,getdate()), 0)

Start of Current Month

cast(convert(char(7),getdate(),23)+'-01' as datetime)

End of Current Month

dateadd(d,-1,dateadd(m,1,cast(convert(char(7),getdate(),23)+'-01' as datetime)))

Start of Previous Month

dateadd(m,-1,cast(convert(char(7),getdate(),23)+'-01' as datetime))

End of Previous Month

dateadd(d,-1,cast(convert(char(7),getdate(),23)+'-01' as datetime))

Start of Current Quarter

dateadd(qq,datediff(qq,0,getdate()),0)

End of Current Quarter

dateadd(qq,datediff(qq,-1,getdate()),-1)

Start of Previous Quarter

dateadd(qq,datediff(qq,2,getdate()),0)

End of Previous Quarter

dateadd(qq,datediff(qq,0,getdate()),-1)

Start of Current Year

dateadd(yy, datediff(yy,0,getdate()), 0)

End of Current Year

dateadd(yy, datediff(yy,-1,getdate()), -1)

Start of Previous Year

dateadd(yyyy,-1,dateadd(yy, datediff(yy,0,getdate()), 0))

End of Previous Year

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


Mark P :uk: (BOB member since 2003-02-03)

Universe - userResponse date and calculated date using that
HOW TO INSERT PREVIOUS YEAR IN LIST OF VALUES
Dynamic date ?
Auto-fill Period Prompt
limit data in automated report
Scheduling Reports
default any date to end of month
Dynamic Filters/Prompts
Create a case statement for last 12 months
Using todays date in prompt for a scheuled report
How to Auto-Schedule with a Dynamic Date Parameter
DATEDIFF
Retrieving QTD; MTD; WTD
SQL in Multisource universe in IDT...problems.
GetDate
Create Current Quarter, Prev Quarter condition objects
BO 4.0 - how to make a re-usable, complex, optional prompt
Dynamic Report Variable: Last Full Months Sales
Previous Month reporting object
Firstdate of Previous Month
Report Filter and YTD calculation
Universe FirstDay of PreviousWeek & LastDay of CurrentW
IDT Date prompt implementation
Date Objects in Universe
multi server problem using IDT
How writen current date sql query in universe?
WEBI Recurring Instances to run for previous month
Relative dates in universe 4.0 using sql server 2008
past 12 months data in a column chart
How to create a object to find maximum day in a week
Last day of month
Scheduling reports to fetch the past 30 days
Filter for Yesterday
Date Range Table
Dynamic filter
Some Date functions for Netezza
Date Filters
Date Objects in Universe
Issue with pre-defined filter at Universe
Last complated full week condition
How to Create YTD Filter in Universe?
Webi report auto schedule on 4 random dates
Setting Universe Conditions
Filtering the Last Month & Last Month Previous Year
Date functions with multi-source universes
based on date need a quarter currnt month in sql server
Dynamic Quater Calculation
Scheduling report with prior month's results
How to display previous month and last 12 month?
Prior Month Quantity Sold Calculation
Filter data on the query
Question related to Yesterday data ?
query regarding pulling last 2 months data and current qrter
Sql Server Date object
universe object and predefined condition
Week To Date, Month To Date
Date Issue
Calculate Yearto Month
Custom Filter on Webi XI 3.1
need to calculate data for the last previous 6 months
Webi Offset Variables
Sysdate Causing Issues
How to create a prompt to get previous month
Relative Date with Time - SQL Server 2000
Remove Timestamp
last six months data in the schedule report
Date Parameters
SQL not parsed as before? why am I getting this error?
Convert Year object from Numeric to Character Type
Date Error
How to create Dynamic Report
How to create Dynamic Report
Pre-Define Object in Universe for monthly scheduling -
Help with defining a Current Quarter Measure
how to Create a Variable for previous month?
Retrieve Records within Last X months up to Now
Current Week day
Constants Date table in BusinessObjects
3 scenarios: Daily(Curr mnt), Monthy(Last12 mnts), Grd total
Creating date objects for a universe
BI 3.1 Universe - Custom date filters?
X Number of Days from Current Date
How to assign date range when running on the fly....
Date
Need Last Month Data in Range Prompt
New object in Universe
year to date
last 12 month data
Formula to calculate as of date
Quarterly Object
create a dynamic report based on a prompted date
Using a formula within a query's condition
"start of last year " formula
BOXI R2 Relative Date (Oracle)
Moving Date Filters
Current Date
How to make predifined filter
last month
sysdate with SQL server05
Howto create derivedtable for sysdate,sysdate-1 in Designer?
Issue trying to hard code a current Date Prompt Query
Year to Date Measure
Universe filter
Detail Object
previous month filter syntax for sql server 2008
Dynamic Date
Previous date data
Predefined Filter for MTD and YTD
Formula to find First Day of Previous Year
BO Date Functions
Condition in Universe
Predefined condition to get previous working day data
yearmonth
current date in Universe level object
Date Prompt and Condition in Query Filter Pane
Date functions in Universe Designer
Predefined Object in universe
Syntax for Previous Month name from Getdate in SQL syntax
Date Objects in the universe - It's a SQL Server DB
YTD offset by 2 months
Report filter with variable or universe object
Date and Time Prompt for a Multisource Universe
Date Formats in Dervied Table Joins-SQL Server -Oracle
Date functions not working in multi source universes
Function Date
Inserting an independent date
SQL Server native SQL in universe

Mark,

I think these would make good stickys?


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

Oracle Relative Dates

Yesterday:

trunc(sydate)-1

Today:

trunc(sysdate)

Start of Current Month:

trunc(sysdate,'mm')

End of Current Month:

last_day(sysdate)

Start of Previous Month:

trunc((trunc(sysdate,'mm')-1),'mm')

End of Previous Month:

trunc(sysdate,'mm')-1

Start of Current Quarter:

trunc(sysdate,'q')

End of Current Quarter:

add_months(trunc(sysdate,'q'),3)-1

Start of Previous Quarter:

trunc(trunc(sysdate,'q')-1,'q')

End of Previous Quarter:

trunc(sysdate,'q')-1

Start of Current Year:

trunc(sysdate,'y')

End of Current Year:

add_months(trunc(sysdate,'y'),12)-1

Start of Previous Year:

trunc(trunc(sysdate,'y')-1,'y')

End of Previous Year:

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


Mark P :uk: (BOB member since 2003-02-03)

How about collecting the Oracle stuff (and any other database too) and make this a “Relative Dates” rather than SQL Server only?


Dave Rathbun :us: (BOB member since 2002-06-06)

Good Plan Dave 8) .

One other thing that should have sticky status, IMO, is Reporter blokes date checklist.
The amount of people I have lead to that:-
https://bobj-board.org/t/84923

Cheers,

Mark.


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

So make it a “Dates” sticky instead of “Relative Dates”, that’s fine too.


Dave Rathbun :us: (BOB member since 2002-06-06)

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 8) .

I’m not saying we should have too many, as that would defeat the object ;). 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.


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

Dave,

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

Regards,
Mark


Mark P :uk: (BOB member since 2003-02-03)

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

Thanks,
Zaif


zaif235 :us: (BOB member since 2010-06-15)

That’s a whole different topic Zaif.

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


Mark P :uk: (BOB member since 2003-02-03)

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. :slight_smile:

Yesterday:

CURRENT_DATE-1

Today:

CURRENT_DATE

Start of Current Month

CURRENT_DATE+1-EXTRACT("day" FROM CURRENT_DATE)

End of Current Month

add_months(CURRENT_DATE+1-EXTRACT("day" FROM CURRENT_DATE),1)-1

Start of Previous Month

add_months(CURRENT_DATE+1-EXTRACT("day" FROM CURRENT_DATE),-1)

End of Previous Month

CURRENT_DATE-EXTRACT("day" FROM CURRENT_DATE)

Start Of Current Year

cast(EXTRACT("year" FROM CURRENT_DATE)||'-01-01' as date)

End Of Current Year

add_months(cast(EXTRACT("year" FROM CURRENT_DATE)||'-01-01' as date),12)-1

Start Of Previous Year

add_months(cast(EXTRACT("year" FROM CURRENT_DATE)||'-01-01' as date),-12)

End Of Previous Year

cast(EXTRACT("year" FROM CURRENT_DATE)||'-01-01' as date)-1

Mark P :uk: (BOB member since 2003-02-03)

Hi there! Total newbie to this forum, so please be kind. :wave:

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


sydneyisle12 (BOB member since 2013-01-10)

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


kbrazell :us: (BOB member since 2003-08-19)

It’s actually quite easy, here’s a few samples:

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.


JPetlev (BOB member since 2006-11-01)

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? :slight_smile:


sydneyisle12 (BOB member since 2013-01-10)

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?

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… :oops:

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 :frowning: Eventually we added both Middle Initial and Name Suffix (Jr vs III) to seperate out the brothers.


JPetlev (BOB member since 2006-11-01)

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. :hb:

What I want to end up with is something to the effect of “SELECT FROM 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.


sydneyisle12 (BOB member since 2013-01-10)

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 :slight_smile:

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.


JPetlev (BOB member since 2006-11-01)

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

@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


sydneyisle12 (BOB member since 2013-01-10)

Umm would this work ?

@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.


JPetlev (BOB member since 2006-11-01)