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

[MarkP]

@chiclothe

I think these would make good stickys?

It already is.

[system]

Netezza Relative Dates

Today

current_date

Yesterday

current_date - 1

Start of Current Month

add_months(current_date,0) - date_part('day', current_date)+1

End of Current Month

add_months(date_trunc('month',current_date),1)-1

Start of Previous Month

add_months(current_date,-1) - date_part('day', add_months(current_date,-1))+1

End of Previous month

add_months(current_date,0) - date_part('day', current_date)

Start of Current Quarter

date_trunc('quarter',current_date)

End of Current Quarter

add_months(date_trunc('quarter',current_date),3)-1

Start of Previous Quarter

date_trunc('quarter',current_date)

End of Previous Quarter

date_trunc('quarter',current_date)-1

Start of Current Year

date_trunc('year',current_date)

End of Current Year

add_months(date_trunc('year',current_date),12)-1

Start of Previous Year

add_months(date_trunc('year',current_date),-12)

End of Previous Year

date_trunc('year',current_date)-1

Start of 2nd Half of Current Year

add_months(date_trunc('year',current_date),6)

Current Date of Previous Year

add_months(current_date, -12)

Start of Current Month of Previous Year

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

Hope it helps,
Marc

---

marc527 (BOB member since 2005-09-08)

[system]

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

---

keamo (BOB member since 2013-02-22)

[MarkP]

@keamo

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.

[system]

Can you please do that for Teradata as well?

---

p.mangal (BOB member since 2014-01-29)

[MarkP]

[quote:d204e56cf6=“p.mangal”]Can you please do that for Teradata as well?
[/quote]

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

[system]

[quote:ad0d83d59f=“p.mangal”]Can you please do that for Teradata as well?
[/quote]

https://bobj-board.org/t/162166

---

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

[system]

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:

cast(timestampadd('SQL_TSI_DAY',-dayofmonth(now())+1,now()) as date)

(Start of) first day of last month:

cast(timestampadd('SQL_TSI_MONTH',-1,timestampadd('SQL_TSI_DAY',-dayofmonth(now())+1,now())) as date)

(End of) last day of last month:

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:

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:

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:

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:

cast(timestampadd('SQL_TSI_DAY',-n,now()) as date)

(Start of) n months ago:

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

---

Debbie (BOB member since 2005-03-01)

[system]

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

---

NycPriya (BOB member since 2010-11-04)

[system]

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

multi-source oracle/SQL Server BI4 IDT:

Multi source universe only.

---

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

[MarkP]

@NycPriya

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.

[system]

These are the ones i built before asking debbie.

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.

---

NycPriya (BOB member since 2010-11-04)

[system]

Try here for Terradata:-
https://bobj-board.org/t/162166

---

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

[system]

@NycPriya

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…

---

Debbie (BOB member since 2005-03-01)

[MarkP]

@NycPriya

These are the ones i built before asking debbie.

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.

[system]

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?

---

brandonwilhelm (BOB member since 2015-03-23)

[system]

Welcome to B:bob:B!

@brandonwilhelm

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:

Random records

Hi, We have a requirement, users want to be able to access/pull random records (like maybe 100 records at a time) in the report. How can I achieve this? Thanks :? eye_candy (BOB member since 2005-03-18)

---

Marek Chladny (BOB member since 2003-11-27)

[system]

@Debbie

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 .

---

charlie (BOB member since 2002-08-20)

[system]

I’ll go back and add the link to my original post for future reference …

debbie

---

Debbie (BOB member since 2005-03-01)

[system]

And at last, something for DB2

DB2 Relative Dates

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

---

Marek Chladny (BOB member since 2003-11-27)