BusinessObjects Board

Teradata relative date functions

building semantic layer
#1

To add to the existing Oracle and SQL Server lists, here are my versions of the Teradata equivalents. Perhaps after any discussion and validation, they can be added to the same ‘Sticky’:

Yesterday:

DATE-1

Today:

DATE

Start of Current Month:

(DATE - DAYOFMONTH(DATE))+1

End of Current Month:

ADD_MONTHS((DATE - DAYOFMONTH(DATE))+1,1)-1

Start of Previous Month:

ADD_MONTHS((DATE - DAYOFMONTH(DATE))+1,-1)

End of Previous Month:

(DATE - DAYOFMONTH(DATE))

Start of Current Quarter:

CASE WHEN MONTH(Date) BETWEEN 1 AND 3 
         THEN CAST(((Date / 10000) * 10000) + 101 AS DATE) 
         WHEN MONTH(Date) BETWEEN 4 AND 6 
         THEN CAST(((Date / 10000) * 10000) + 401 AS DATE) 
         WHEN MONTH(Date) BETWEEN 7 AND 9 
         THEN CAST(((Date / 10000) * 10000) + 701 AS DATE) 
         WHEN MONTH(Date) BETWEEN 10 AND 12 
         THEN CAST(((Date / 10000) * 10000) + 1001 AS DATE) 
END

End of Current Quarter:

CASE WHEN MONTH(Date) BETWEEN 1 AND 3 
         THEN CAST(((Date / 10000) * 10000) + 331 AS DATE) 
         WHEN MONTH(Date) BETWEEN 4 AND 6 
         THEN CAST(((Date / 10000) * 10000) + 630 AS DATE) 
         WHEN MONTH(Date) BETWEEN 7 AND 9 
         THEN CAST(((Date / 10000) * 10000) + 930 AS DATE) 
         WHEN MONTH(Date) BETWEEN 10 AND 12 
         THEN CAST(((Date / 10000) * 10000) + 1231 AS DATE) 
END

Start of Previous Quarter:

ADD_MONTHS(CASE WHEN MONTH(Date) BETWEEN 1 AND 3 
         THEN CAST(((Date / 10000) * 10000) + 101 AS DATE) 
         WHEN MONTH(Date) BETWEEN 4 AND 6 
         THEN CAST(((Date / 10000) * 10000) + 401 AS DATE) 
         WHEN MONTH(Date) BETWEEN 7 AND 9 
         THEN CAST(((Date / 10000) * 10000) + 701 AS DATE) 
         WHEN MONTH(Date) BETWEEN 10 AND 12 
         THEN CAST(((Date / 10000) * 10000) + 1001 AS DATE) 
END,-3)

End of Previous Quarter:

(CASE WHEN MONTH(Date) BETWEEN 1 AND 3 
         THEN CAST(((Date / 10000) * 10000) + 101 AS DATE) 
         WHEN MONTH(Date) BETWEEN 4 AND 6 
         THEN CAST(((Date / 10000) * 10000) + 401 AS DATE) 
         WHEN MONTH(Date) BETWEEN 7 AND 9 
         THEN CAST(((Date / 10000) * 10000) + 701 AS DATE) 
         WHEN MONTH(Date) BETWEEN 10 AND 12 
         THEN CAST(((Date / 10000) * 10000) + 1001 AS DATE) 
END)-1

Start of Current Year:

(DATE-DAYOFYEAR(DATE))+1

End of Current Year:

ADD_MONTHS(DATE-DAYOFYEAR(DATE)+1,12)-1

Start of Previous Year:

ADD_MONTHS((DATE-DAYOFYEAR(DATE))+1,-12)

End of Previous Year:

DATE-DAYOFYEAR(DATE)

Current Working Week Start Day (which would be a Monday):

(DATE-DAYOFWEEK(DATE))+2

Prior Working Week Start Day (which would be last Monday):

(DATE-DAYOFWEEK(DATE))-5

Prior Working Week End Day (which would be a Friday):

(DATE-DAYOFWEEK(DATE))-1

anorak :uk: (BOB member since 2002-09-13)

YTD and MTD in webi Prompts
DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2
DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2
last 12 month data
Previous Filter - Teradata
How to populate the month/year object in the designer
BO Date Functions
#2

Hi,

Can you please explain the date functions for start and end of current quarter and start and end of previous quarter?

Thanks

Nisha Kothari (BOB member since 2009-10-16)

#3

Okay, I think what I’m saying below is correct…

Taking the start of the current quarter as an example:

CASE WHEN MONTH(Date) BETWEEN 1 AND 3 
         THEN CAST(((Date / 10000) * 10000) + 101 AS DATE) 
         WHEN MONTH(Date) BETWEEN 4 AND 6 
         THEN CAST(((Date / 10000) * 10000) + 401 AS DATE) 
         WHEN MONTH(Date) BETWEEN 7 AND 9 
         THEN CAST(((Date / 10000) * 10000) + 701 AS DATE) 
         WHEN MONTH(Date) BETWEEN 10 AND 12 
         THEN CAST(((Date / 10000) * 10000) + 1001 AS DATE) 
END

Today’s date is 4th May 2011, which is held in Teradata as 1110504. Because the Month number is 5, the following part of the CASE statement is used:

THEN CAST(((Date / 10000) * 10000) + 401 AS DATE)

Dividing 1110504 by 10000 gives 111 (Teradata must truncate the result), and this multiplied by 10000 gives 1110000. Adding 401 results in 1110401; the 1st of April, as the start of the current quarter.

Hope that this makes sense.

A Teradata expert can probably explain it a lot better than me!

anorak :uk: (BOB member since 2002-09-13)

#4

Got it!!

Thanks a lot Anurak! I understood all the functions except the week ones.

Can you please explain “First day of the week”, Last day of previous week and first day of previous week". I didn’t understand the numbers part. I know that you have to add some number to get the day of the current week and subtract some number to get the day from previous week.

Eg: For first day of the week, if the current date is Wednesday i.e 4 May 2011 how do you get the first day?

Thanks in advance,
nisha

Nisha Kothari (BOB member since 2009-10-16)

#5

Nisha,

Please note that these functions refer to the working week. In Teradata, Sunday is the 1st day of the week. Today is the 4th day of the week. The calculation for the 1st day of the current working week is:

(DATE-DAYOFWEEK(DATE))+2

So, for this week: (4/5/2011 (dd/mm/yyyy)-4)+2 gives 2/5/2011 which is Monday’s date. (i.e. the start of the working week)

Actually, it does not work for this week, as in the U.K., Monday was a national holiday! These weekly calculations do not take national holidays into account.

anorak :uk: (BOB member since 2002-09-13)

#6

Thanks a lot for your reply! I have understood all of them.

Nisha Kothari (BOB member since 2009-10-16)

#7

Hi can you please help me to find out last day of the week?(last day of week is sunday). i am using teradata database+boxir3.1

Thanks
bouser86

BOUSER86 (BOB member since 2010-10-04)

#8

I don’t have access to Teradata anymore (currently looking for a contract :frowning: ), but the following should work:


CASE
WHEN DAYOFWEEK(DATE)=1
THEN DATE
ELSE (DATE-DAYOFWEEK(DATE))+8
END

anorak :uk: (BOB member since 2002-09-13)