Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Search by Month, Day Or Year

    Hi all I would like to give users the options to either do a search by Month, day or year. See with this function users can look up beginning and ending dates for renewals, this way they can generate thier own reports. But I would like to give them the option of entering either the Month, day or year? How would I go about doing that??

    Code:
    ALTER FUNCTION [dbo].[Beginning_EndingFinals](@StartFINALSUITDONE datetime,
    @EndFINALSUITDONE datetime)
    RETURNS TABLE
    AS
    RETURN ( SELECT     TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, FINALSUITDONE
    FROM         dbo.EmployeeGamingLicense
    WHERE     (FINALSUITDONE BETWEEN @StartFINALSUITDONE AND @EndFINALSUITDONE) )

  2. #2
    Join Date
    Apr 2007
    Posts
    183
    Code:
    ALTER FUNCTION dbo.Beginning_EndingFinals
    (
    	@Interval VARCHAR(7),
    	@ThisTime DATETIME
    )
    RETURNS @Result TABLE (column definition here)
    AS
    BEGIN
    	DECLARE	@Start DATETIME,
    		@End DATETIME
    
    	IF @Interval = 'YEAR'
    		SELECT	@Start = DATEADD(YEAR, DATEDIFF(YEAR, '19000101', @ThisTime), '19000101'),
    			@End = DATEADD(YEAR, DATEDIFF(YEAR, '18991231', @ThisTime), '19000101')
    		END
    	ELSE IF @Interval = 'QUARTER'
    		SELECT	@Start = DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', @ThisTime), '19000101'),
    			@End = DATEADD(QUARTER, DATEDIFF(QUARTER, '18991231', @ThisTime), '19000101')
    		END
    	ELSE IF @Interval = 'MONTH'
    		SELECT	@Start = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @ThisTime), '19000101'),
    			@End = DATEADD(MONTH, DATEDIFF(MONTH, '18991231', @ThisTime), '19000101')
    	ELSE IF @Interval = 'WEEK'
    		SELECT	@Start = DATEADD(WEEK, DATEDIFF(WEEK, '19000101', @ThisTime), '19000101'),
    			@End = DATEADD(WEEK, DATEDIFF(WEEK, '19000101', @ThisTime), '19000108')
    	ELSE -- Default to 'DAY' if no valid value is provided
    		SELECT	@Start = DATEADD(DAY, DATEDIFF(DAY, '19000101', @ThisTime), '19000101'),
    			@End = DATEADD(DAY, DATEDIFF(DAY, '19000101', @ThisTime), '19000102')
    
    	INSERT	@Result
    		(column definition here)
    	SELECT	TM#,
    		LASTNAME,
    		FIRSTNAME,
    		CONDITIONAL,
    		DATEOFCONDITIONAL,
    		FINALSUITDONE
    	FROM	dbo.EmployeeGamingLicense
    	WHERE	FINALSUITDONE >= @Start
    		AND FINALSUITDONE  < @End
    
    	RETURN
    END

  3. #3
    Join Date
    Apr 2007
    Posts
    183
    Call with SELECT * FROM dbo.Beginning_EndingFinals(
    'Month', getdate())

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    I must be doing something wrong because I am geting some serious error messages. It keeps saying incorrect syntax

    Nevermind disregard please
    Last edited by desireemm; 10-06-08 at 19:33.

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Nevermind problem solved, I made it allot harder then what it really was

Posting Permissions

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