Recipe 9.2. Determining the Number of Days in a YearProblemYou want to count the number of days in the current year. SolutionThe number of days in the current year is the difference between the first day of the next year and the first day of the current year (in days). For each solution the steps are:
The solutions differ only in the built-in functions that you use to perform these steps. DB2Use the function DAYOFYEAR to help find the first day of the current year, and use DAYS to find the number of days in the current year: 1 select days((curr_year + 1 year)) - days(curr_year) 2 from ( 3 select (current_date - 4 dayofyear(current_date) day + 5 1 day) curr_year 6 from t1 7 ) x OracleUse the function TRUNC to find the beginning of the current year, and use ADD_ MONTHS to then find the beginning of next year: 1 select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y') 2 from dual PostgreSQLUse the function DATE_TRUNC to find the beginning of the current year. Then use interval arithmetic to determine the beginning of next year: 1 select cast((curr_year + interval '1 year') as date) - curr_year 2 from ( 3 select cast(date_trunc('year',current_date) as date) as curr_year 4 from t1 5 ) x MySQLUse ADDDATE to help find the beginning of the current year. Use DATEDIFF and interval arithmetic to determine the number of days in the year: 1 select datediff((curr_year + interval 1 year),curr_year) 2 from ( 3 select adddate(current_date,-dayofyear(current_date)+1) curr_year 4 from t1 5 ) x SQL ServerUse the function DATEADD to find the first day of the current year. Use DATEDIFF to return the number of days in the current year: 1 select datediff(d,curr_year,dateadd(yy,1,curr_year)) 2 from ( 3 select dateadd(d,-datepart(dy,getdate())+1,getdate()) curr_year 4 from t1 5 ) x DiscussionDB2The first step is to find the first day of the current year. Use DAYOFYEAR to determine how many days you are into the current year. Subtract that value from the current date to get the last day of last year, and then add 1: select (current_date dayofyear(current_date) day + 1 day) curr_year from t1 CURR_YEAR ----------- 01-JAN-2005 Now that you have the first day of the current year, just add one year to it; this gives you the first day of next year. Then subtract the beginning of the current year from the beginning of next year. OracleThe first step is to find the first day of the current year, which you can easily do by invoking the built-in TRUNC function and passing 'Y' as the second argument (thereby truncating the date to the beginning of the year): select select trunc(sysdate,'y') curr_year from dual CURR_YEAR ----------- 01-JAN-2005 Then add one year to arrive at the first day of the next year. Finally, subtract the two dates to find the number of days in the current year. PostgreSQLBegin by finding the first day of the current year. To do that, invoke the DATE_ TRUNC function as follows: select cast(date_trunc('year',current_date) as date) as curr_year from t1 CURR_YEAR ----------- 01-JAN-2005 You can then easily add a year to compute the first day of next year. Then all you need to do is to subtract the two dates. Be sure to subtract the earlier date from the later date. The result will be the number of days in the current year. MySQLYour first step is to find the first day of the current year. Use DAYOFYEAR to find how many days you are into the current year. Subtract that value from the current date, and add 1: select adddate(current_date,-dayofyear(current_date)+1) curr_year from t1 CURR_YEAR ----------- 01-JAN-2005 Now that you have the first day of the current year, your next step is to add one year to it to get the first day of next year. Then subtract the beginning of the current year from the beginning of the next year. The result is the number of days in the current year. SQL ServerYour first step is to find the first day of the current year. Use DATEADD and DATEPART to subtract from the current date the number of days into the year the current date is, and add 1: select dateadd(d,-datepart(dy,getdate())+1,getdate()) curr_year from t1 CURR_YEAR ----------- 01-JAN-2005 Now that you have the first day of the current year, your next step is to add one year to it get the first day of the next year. Then subtract the beginning of the current year from the beginning of the next year. The result is the number of days in the current year. |