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.

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.