Year of week

[devendrap]

Hi Can some one help me how to find week starting from mon to sat etc..

For Week of Year function

My Sql Week(date) is giving week starting from Sun to sat.

Is it possible to change week starting.

Thank you

 

[tdatgod]

Hi,
Teradata has a database called SYS_CALENDAR which contains 1 table

SYS_CALENDAR.CALDATES

with every date between Jan 1, 1900 and Dec 31, 2100. Then it has 3 views for defining stuff like Day_of_week, week_of_year, day_of_year.

CALBASICS
CALENDARTMP
CALENDAR

the only one of use is

SYS_CALENDAR.CALENDAR

since it simplifies the inclusion of the other 2 views to well known column names although all the formulas for calulating everything are hidden inside

SYS_CALENDAR.CALENDARTMP;


you typically do

sel week_of_year from SYS_CALENDAR.CALENDAR
where calendar_date = date;

Your problem is week_of_YEAR is based SUN to Sat and you need Mon to Sunday.


I suspect that you could probably define your own view in your own database which is based upon

SYS_CALENDAR.CALENDAR


something like.....

Replace view mycalendar
(
calendar_date,
day_of_week,
day_of_month,
day_of_year,
day_of_calendar,
weekday_of_month,
week_of_month,
week_of_year,
week_of_calendar,
month_of_quarter,
month_of_year,
month_of_calendar,
quarter_of_year,
quarter_of_calendar,
year_of_calendar,
week_of_year_monday_based,
)
as
sel calendar_date,
day_of_week,
day_of_month,
day_of_year,
day_of_calendar,
weekday_of_month,
week_of_month,
week_of_year,
week_of_calendar,
month_of_quarter,
month_of_year,
month_of_calendar,
quarter_of_year,
quarter_of_calendar,
year_of_calendar,
(day_of_year - (day_of_calendar + 1) mod 7 + 6) / 7
from SYS_CALENDAR.CALENDAR;


now you could

sel week_of_year_monday_based
from MYCALENDAR
where calendar_date = date;

or something like that. ( I haven't actually tried the formula above so if it's wrong you will have to create your own to make the correct output. )


If you need some other date value just look at the table and view defintions under SYS_CALENDAR and modify them accordingly.

I won't advise changing the Teradata System views or putting your view inside SYS_CALENDAR database since when you upgrade the upgrade may delete and recreate the whole database and/or views and therefore your modification may be lost.

Hope this helps.

 

[devendrap]

Thank you That really helps.

I am able to get weeks as below :
Date Week DAY
2001-01-01 00 Mon
2001-01-02 00 The
2001-01-03 00 Wen
2001-01-04 01 THU
2001-01-05 01 Fri

Here First week which is the part of last years week.
Is it possible to get like...

Date Week DAY
2001-01-01 52 Mon
2001-01-02 52 The
2001-01-03 52 Wen
2001-01-04 01 THU
2001-01-05 01 Fri
I tried week(date)- 1 but it is not consistence with this calendar....

Thanks again..

 

[tdatgod]

Hi,
Could you add 52 and mod by 53?

--

 

[devendrap]

How to add and mod 53...

 

[tdatgod]

( ( (your formula ) + 52 ) mod 53 )


this makes 0 = 52, 1 = 0; 2 = 1,

it would make -1 = 51