Database
Not applicable

## 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.