DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

building semantic layer
1

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

2

Mark,

I think these would make good stickys?

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

3

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

4

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)

5

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)

6

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

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

7

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)

8

Dave,

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

Regards,
Mark

9

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

Thanks,
Zaif

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

10

That’s a whole different topic Zaif.

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

11

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
12

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)

13

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: UTC+05:30 - Wikipedia

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

I started here: Time zone - Wikipedia

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

14

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)

15

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)

16

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)

17

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)

18

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)

19

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)

20

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)