Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: What's wrong with this FUNCTION

    I created a function that returns YYYYWW based on our fiscal calendar. It gives me a NULL value for today's date.


    print getdate()
    Jul 30 2011 12:10PM <---- date today


    select dbo.biweekno(getdate())
    NULL <------ Why NULL?

    select dbo.biweekno('07/30/2011')
    201204 <------- entered the date today. OK!

    *** SAMPLE DATA BELOW***
    *BIYear * BIMonth * BIWeek * BIWeekNo * StartDate * EndDate
    * 2012 * 1 * 4 * 201204 * 2011-07-24 00:00:00 * 2011-07-30 00:00:00 *
    * 2012 * 2 * 5 * 201205 * 2011-07-31 00:00:00 * 2011-08-06 00:00:00 *









    *****
    CREATE FUNCTION [dbo].[BIWeekNo] (@Date SmallDateTime)
    RETURNS Varchar(6)
    AS
    BEGIN
    DECLARE @BIWeekNo Varchar(6)

    SELECT @BIWeekNo=BIWeekNo FROM BICalendar WHERE @Date BETWEEN StartDate and EndDate
    RETURN @BiWeekNo

    END
    *****

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    all of the 30th of july (except for the exact second of midnight) is missing from your calendar table

    you have the same issue with the next week as well, for aug 6th

    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
  •