Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2007
    Posts
    29

    Red face Unanswered: determine days of the year

    Hi there, is that any function in ms sql server 2000 where if i pass a date or a year then it could gives me the total days of year from the parameter?
    in mySQL got select DAYOFYEAR(date);

    can some one guide me on this please...i need to use it for a leap year function for my SP!
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    There's no built-in function that will return the number of days in a year for a given date.However you can create a function something like

    Code:
    CREATE FUNCTION [dbo].[GetDaysInYear] ( @pDate    DATETIME )
    RETURNS INT
    AS
    BEGIN
    
        DECLARE @IsLeapYear        BIT
    
        SET @IsLeapYear = 0
        IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
            YEAR( @pDate ) % 400 = 0
            SET @IsLeapYear = 1
    
        RETURN 365 + @IsLeapYear
    
    END
    GO
    Then you can call your function directly in your SP like

    GetDaysInYear (Your date goes here)

  3. #3
    Join Date
    May 2007
    Posts
    49
    HTML Code:
    CREATE FUNCTION GetDaysInYear 
    ( 
    	@pDate DATETIME 
    )
    RETURNS INT
    AS
    BEGIN
    	Declare @days int
    	Declare @firstDay varchar(15)
    	set @firstDay = '1/1/' + Cast(Year(@pDate) as varchar)
    	Select @days = DATEDIFF(dd, Convert(Varchar, @firstDay, 101), @pDate)
        RETURN @days
    END
    GO
    This function behaves similar to MySQL DAYOFYEAR(date) function
    (only diff. is I think with 1-Jan)
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mihir, you were close but the calculation is off by 1 for all dates

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2007
    Posts
    29
    Hi Thanx nick.ncs

    i think i can just use your :

    IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
    YEAR( @pDate ) % 400 = 0
    it wats i need after all!
    thank you so much nick.ncs
    Thanks & Regards!
    dev
    !==============================================!
    Live your life to the fullest Whilst u can...=_="
    Alwayz Aim for the moon. If you miss, you may Hit a star....-_+~
    Where there's a will, There's a way....;^D
    !==============================================!

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    another way is to check if the 60th day of the year is in feb. see:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83637

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    declare @dt datetime
    set @dt='2007-06-18'
    select datediff(dd,
    dateadd(yy,datediff(yy,0,@dt),0)
    ,dateadd(yy,datediff(yy,0,@dt)+1,0)
    ) DaysInYear

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pdreyer, that is gorgeous

    wherever did you learn that?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    select datepart(dy, getdate())

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    mcrowley, welcome to the thread

    please note your query produces day in year

    the problem was days in year

    i.e. either 365 or 366

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    but your idea was a good one

    Code:
    select datepart(dy, dateadd(dd,-1,dateadd(yy,datediff(yy,0,@dt)+1,0)))
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Doh !

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    don't beat yourself up about it, you still had a great idea, but it would only have worked for Dec 31st, so that's what i changed it slightly to do

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •