All Forums Teradata Applications
keerthi1987 14 posts Joined 02/16
01 May 2016
Set Default week start day as Friday in Teradata

We are generating weekly based customers report in application.In that previously we have used default in teradata,but We need to set defualt week start day as Friday that should be globle.
Example In Sql server,
SELECT DATEPART(WEEK,CAST('2015-01-01'AS DATE)) AS WeekNumber From UserTable;
We need equvalent query inTeradata.Can anyone help on this. 

dnoeth 4628 posts Joined 11/04
01 May 2016

There's no concept of a DATEFIRST is Teradata and the built-in calendars support only a variation of calendar where the first partial week of a year is week 0 or ISO Standard weeks or Oracle compatible (strange) week numbers .
Date calculations like this should be done either within the application or based on the company's calendar table.
As a workaround you can implement SQL Server week logic like this:

((TD_FRIDAY(dt) - TRUNC(dt, 'year')) / 7) + 1

TD_FRIDAY returns the latest Friday up to the supplied date, TRUNC returns Jan 1st, so this numbers weeks based on Friday as starting day of weeks, Jan 1st always in week 1.


You must sign in to leave a comment.