Database Welcome to the Teradata Forums. Read the guidelines on posting. Email notifications and RSS feeds are available, and also a chronological list of all forum activity.
 Print All Forums Database sandy1690 1 post Joined 03/14 17 Mar 2014 Calculating day number of the year for a date Hi all, I need to know the syntax for calculating the day number of the year for a date in teradata SQL. Tags: krishaneesh 140 posts Joined 04/13 17 Mar 2014 `sel calendar_date,day_of_year from sys_calendar.calendar`This gives the day of the year krishaneesh 140 posts Joined 04/13 17 Mar 2014 ```sel  (case (current_date mod 10000)/100      when 1  then current_date mod 100      when 2  then current_date mod 100 + 31      when 3  then current_date mod 100 + 59      when 4  then current_date mod 100 + 90      when 5  then current_date mod 100 + 120      when 6  then current_date mod 100 + 151      when 7  then current_date mod 100 + 181      when 8  then current_date mod 100 + 212      when 9  then current_date mod 100 + 243      when 10 then current_date mod 100 + 273      when 11 then current_date mod 100 + 304      when 12 then current_date mod 100 + 334   end)   +   (case     when ((((current_date / 10000 + 1900) mod 4 = 0) AND ((current_date / 10000 + 1900) mod 100 <> 0)) OR          ((current_date / 10000 + 1900) mod 400 = 0)) AND ((current_date mod 10000)/100 > 2) then       1     else       0   end)```This will work only for dates from 1900-01-01 to 2100-12-31. This is an extension of the above YouriD 9 posts Joined 01/14 18 Mar 2014 slightly shorter calculation (in case you don't want to use the calendar-table): ```sel   date - cast(trim(extract(year from date)) || '/01/01' as date format 'YYYY/MM/DD') + 1```  I'm calculating the number of days between the wanted date and the first day of the year of that date + 1 because the first day of the year needs to be counted as well   regards   Youri VandeBergB 182 posts Joined 09/06 18 Mar 2014 Youri, Keep it simple, use the sys_calendar.calendar view.  It will keep you covered for the the next 87 years, and the first day of the year is already  numbered one so you don't even have to do the math of adding 1. keep it simple, the code you have to maintain may be your own. Cheers. Some drink from the fountain of knowledge, others just gargle. Fred 1096 posts Joined 08/04 18 Mar 2014 In TD14.0 and later, use td_day_of_year() function You must sign in to leave a comment. Active Posters