Date Validation using ISDATE()

  • Hello,

    I create a stored procedure and I am attempting to validate my @DateMax and @DateMin parameters. Both parameters have a varchar data type. If the user provides an inappropriate date like: '13/21/08' then I want to raise an error "Invalid Date". I am getting these errors when creating the procedure.

    Msg 111, Level 15, State 1, Procedure spDateRange, Line 9

    'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

    Msg 156, Level 15, State 1, Procedure spDateRange, Line 14

    Incorrect syntax near the keyword 'If'.

    Msg 156, Level 15, State 1, Procedure spDateRange, Line 19

    Incorrect syntax near the keyword 'If'.

    Below is my code. The first 2 rules look at the date format.........Thanks for any help.

    USE AP

    GO

    If Object_ID ('spDateRange') IS NOT NULL

    DROP PROC spDateRange

    GO

    select ISDATE ('5/1/2008')

    CREATE PROCspDateRange

    @DateMin varchar (50) = NULL,

    @DateMax varchar (50) = NULL

    AS

    If ISDATE(@DateMin) = 0

    Raiserror('Please enter valid date format ',11,1)

    Return

    End

    If ISDATE(@DateMax) = 0

    Raiserror('Please enter valid date format ',11,1)

    Return

    End

    If (@DateMin is null) Begin

    Raiserror('Please enter the MinimumDate ',11,1)

    Return

    End

    If (@DateMax is null) Begin

    Raiserror('Please enter the MaximumDate ',11,1)

    Return

    End

    If (@DateMin = @DateMax) Begin

    Raiserror('Minimum Date can''t be equal to the maximum date ',11,1)

    Return

    End

    If (@DateMin > @DateMax) Begin

    Raiserror('Minimum Date can''t be later then maximum date ',11,1)

    Return

    End

    Select InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceTotal-PaymentTotal-CreditTotal AS Balance

    From Invoices

    Where InvoiceDate Between @DateMin and @DateMax

    Order By InvoiceDate

  • Remove the select ISDATE ('5/1/2008') from before the Create Proc

    You're missing begins for two of those ifs

    If ISDATE(@DateMin) = 0

    BEGIN

    Raiserror('Please enter valid date format ',11,1)

    Return

    End

    If ISDATE(@DateMax) = 0

    BEGIN

    Raiserror('Please enter valid date format ',11,1)

    Return

    End

    If (@DateMin is null) Begin

    Raiserror('Please enter the MinimumDate ',11,1)

    Return

    End

    If (@DateMax is null) Begin

    Raiserror('Please enter the MaximumDate ',11,1)

    Return

    End

    If you want to make the parameters required, remove the defaults from the parameter definitions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply