Calculating day number of the year for a date

Database

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 (2)
5 REPLIES
Enthusiast

Re: Calculating day number of the year for a date

sel calendar_date,day_of_year from sys_calendar.calendar

This gives the day of the year

Enthusiast

Re: Calculating day number of the year for a date

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

Enthusiast

Re: Calculating day number of the year for a date

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

Enthusiast

Re: Calculating day number of the year for a date

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.

Teradata Employee

Re: Calculating day number of the year for a date

In TD14.0 and later, use td_day_of_year() function