Day Of Week or Week-Ending Function?

Database

Day Of Week or Week-Ending Function?

I need to rollup and report data by week, with the week being defined as Sun through Saturday and identified as the Saturday-date. Does Teradata SQL have any functions that could facilitate this?

For example, if there was a"WEEKDAY" function as in Access or Excel, I could derive it with date math, but I couldn't find anything like that in my documentation.

Thanks.
Tags (4)
9 REPLIES
Teradata Employee

Re: Day Of Week or Week-Ending Function?

Join Sys_Calendar.Calendar (it should be populated)

calendar_date DATE UNIQUE (Standard Teradata date)
day_of_week BYTEINT, (1-7, where 1 = Sunday)
day_of_month BYTEINT, (1-31)
day_of_year SMALLINT, (1-366)
day_of_calendar INTEGER, (Julian days since 01/01/1900)
weekday_of_month BYTEINT, (nth occurrence of day in month)
week_of_month BYTEINT, (partial week at start of month is 0)
week_of_year BYTEINT, (0-53) (partial week at start of year is 0)
week_of_calendar INTEGER, (0-n) (partial week at start is 0)
month_of_quarter BYTEINT, (1-3)
month_of_year BYTEINT, (1-12)month_of_calendar INTEGER, (1-n) (Starting Jan, 1900)
quarter_of_year BYTEINT, (1-4) quarter_of_calendar INTEGER, (Starting Q1, 1900)
year_of_calendar SMALLINT, (Starting 1900)

Re: Day Of Week or Week-Ending Function?

How to get Today's day in Teradata. I tried to find but couldn't.. Like in SQL we have

select datename(dw,getdate()) -- Gives Friday

This will give the day value!

Any inputs would be appreciated!

Re: Day Of Week or Week-Ending Function?

Try this:

sel case when day_of_week=1 then 'Saunday'

when day_of_week=2 then 'Monnday'

when day_of_week=3 then 'Tuesday'

when day_of_week=4 then 'Wednesday'

when day_of_week=5 then 'Thursday'

when day_of_week=6 then 'Friday'

when day_of_week=7 then 'Saturday'

end "Day"

from sys_calendar.CALENDAR

where calendar_date=date

or

The below query will prompt you to enter the date and it need to be 'YYYY-MM-DD' format.

sel case when day_of_week=1 then 'Saunday'

when day_of_week=2 then 'Monnday'

when day_of_week=3 then 'Tuesday'

when day_of_week=4 then 'Wednesday'

when day_of_week=5 then 'Thursday'

when day_of_week=6 then 'Friday'

when day_of_week=7 then 'Saturday'

end "Day"

from sys_calendar.CALENDAR

where calendar_date='?Date'

Re: Day Of Week or Week-Ending Function?

You can give a try using:

Select day_of_week(date_column1) from Table1;

Thanks,

Ashok.

N/A

Re: Day Of Week or Week-Ending Function?

This works for computing a Friday week ending date:

CASE WHEN ((any_date - (000101 (DATE))) + 1) MOD 7 = 6 THEN any_date + 6

     ELSE   any_date + (6 - (((any_date - (000101 (DATE))) + 1) MOD 7 + 1)) END AS fri_week_ending_date

Re: Day Of Week or Week-Ending Function?

I feel Sys_calendar.calendar is fine , until and unless we are driven to model another calendar, because of geography etc.

Cheers,

Raja

Re: Day Of Week or Week-Ending Function?

Hi Experts,

In addition to the discussion above, I require the calcualtion logic of fin_cal_week , just like we

have cal_week in sys_calendar.calendar table for population values in our date dimension table.

We got the dervation of Finnancial_year, financial_month  but stuck in calculating fin_week .

Can any one pls suggest?

Thanks in advance!

Cheers!

Nishant

Re: Day Of Week or Week-Ending Function?

Hi Nishant,

You can just see (similar to)  in  'show select * from Sys_calendar.calendar' maybe, there you can get the logic of how you can get it. It is something using a mod function divided by 7. Hope it helps.

Cheers,

Raja

N/A

Re: Day Of Week or Week-Ending Function?

SELECT TD_Day_of_Week(Date_Column) FROM Table1;

I think many people have overlooked Ashok's excellent suggestion above, so to reiterate, you can use the Day_of_Week function bundled with Teradata to extract the day as an INTEGER.

Actually, I just discovered it has been replaced with the Function "td_day_of_week", so you should now use this instead (though as of 14.10 "day_of_week" still works):

I'd paste the link but for some reason my browser won't let me paste into this text field, but google "Teradata td_day_of_week" and you should find it.