All Forums Database
NK-8995 8 posts Joined 11/09
14 Dec 2009
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 dateof the particular day.I don't want to use any teradata System defined tables for thisJust one function for which the input parameter is the date and output is day of Week.Regards,Naba

Jimm 298 posts Joined 09/07
14 Dec 2009

You can use date arithmetic: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' ENDFrom ....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.

pawan0608 101 posts Joined 12/07
14 Dec 2009

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

Jimm 298 posts Joined 09/07
14 Dec 2009

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.

Sunar 59 posts Joined 02/08
17 Dec 2009

Hi,Teradata provides the format function.select current_timestamp(0) (FORMAT 'E4,bM4bDD,YYYYbHH:MI:SSbT');Try this....

kishor26 1 post Joined 08/15
20 Aug 2015

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

manishdasin 6 posts Joined 01/12
04 Aug 2016

Please try this:
 

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

 

You must sign in to leave a comment.