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,
SET DATEFIRST 5;
SELECT DATEPART(WEEK,CAST('2015-01-01'AS DATE)) AS WeekNumber From UserTable;
We need equvalent query inTeradata.Can anyone help on this.
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.