Day Name fuction in Teradata

Database
Enthusiast

Day Name fuction in Teradata

Hi,

Is there any day name function in teradata ,

which will fetch me the day name of a week(e.g. Sunday,Monday,etc.) given the date

of the particular day.I don't want to use any teradata System defined tables for this

Just one function for which the input parameter is the date and output is day of Week.

Regards,

Naba
7 REPLIES
Enthusiast

Re: Day Name fuction in Teradata

You can use date arithmetic:

Select
CASE ((Date '2009-12-14' - Date '1900-01-01') Mod 7) + 1
WHEN 0 THEN 'Sun'
WHEN 1 THEN 'Mon'
WHEN 2 THEN 'Tue'
WHEN 3 THEN 'Wed'
WHEN 4 THEN 'Thu'
WHEN 5 THEN 'Fri'
WHEN 6 THEN 'Sat' END
From ....

This will work for any date after your reference date; it gives a null if it is before. (You can always use an earlier reference date; just pick any Monday.
Enthusiast

Re: Day Name fuction in Teradata

some how it is not working at my end, i think something need to change. checked for date '2009-12-05'
Enthusiast

Re: Day Name fuction in Teradata

You are right Pawan - it does not work for dates before the reference date. I have modified the code above so it is OK now.
Enthusiast

Re: Day Name fuction in Teradata

Hi,
Teradata provides the format function.

select current_timestamp(0) (FORMAT 'E4,bM4bDD,YYYYbHH:MI:SSbT');
Try this....

Re: Day Name fuction in Teradata

though this post is old wanted to make a correction in the above code.

in the above code which jimm gave for sunday it should be 7 instead of 0.

Select CASE ((Date '2009-12-14' - Date '1900-01-01') Mod 7) + 1WHEN 0 THEN 'Sun'WHEN 1 THEN 'Mon'WHEN 2 THEN 'Tue'WHEN 3 THEN 'Wed'WHEN 4 THEN 'Thu'WHEN 5 THEN 'Fri'WHEN 6 THEN 'Sat' END

Select CASE ((Date '2009-12-14' - Date '1900-01-01') Mod 7) + 1WHEN 7 THEN 'Sun'WHEN 1 THEN 'Mon'WHEN 2 THEN 'Tue'WHEN 3 THEN 'Wed'WHEN 4 THEN 'Thu'WHEN 5 THEN 'Fri'WHEN 6 THEN 'Sat' END

Enthusiast

Re: Day Name fuction in Teradata

Please try this:

SELECT CAST(current_date AS DATE FORMAT'e4') (CHAR(9));
New Member

Re: Day Name fuction in Teradata

Worked for me