Results 1 to 12 of 12

Thread: vaidate date

  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: vaidate date

    To validate a date of yyyymmdd to make sure the
    date entered is valid. For example '09461002' is not valid date . I had written a procedure by using ISdate function which works fine in SQL server but i need to call from an Application , So please can any body tell me how to return 1 if successfull 0 if unsuccessful. Here is the procedure

    CREATE PROCEDURE date @P_DateString char(8) AS

    DECLARE @datestring varchar(8), @tmp int
    SET @datestring = @P_DateString;
    SELECT ISDATE(@datestring)
    return @tmp;
    GO

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why not just execute the procedure?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    6
    If i execute the procedure , it always returns null from application, i want to return 1 if date is valid else 0 for invalid date

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, sorry, I'm at home...

    SELECT @tmp=ISDATE(@datestring)

    Do you know about output variables as well?

    and actually you can do away with @datestring and just ref the input variable..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Posts
    6
    Hi Brett,
    I tried both output variable and below procedure . The problem is every time it returns 0 instead of 1 or 0. What my question is how to write TRUE or FALSe by using if...ELSE , So that i can set @tmp to write 1 if it is true and 0 if it is false. I tried a function also but no use. Please le tme know if you have any other solution.
    CREATE PROCEDURE date @P_DateString char(8) AS

    DECLARE @datestring varchar(8), @tmp int
    SET @datestring = @P_DateString;
    SET @tmp = ISDATE(@datestring)
    print @tmp
    return @tmp;
    GO

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Have you tried something like:
    PHP Code:
    CREATE PROCEDURE dbo.d221
       
    @pcDate CHAR(8)
    AS

    SELECT IsDate(@pcDate)

    RETURN 
    This will return a result set with a BIT value of one if the date is valid, or zero if it is not. Note that you could just as easily concot the SELECT statement in your client code and bypass the procedure, but that is a matter of taste.

    -PatP

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE PROCEDURE usp_valid_date 
    	@P_DateString char(8)
    	, @Result int OUTPUT 
    AS
    	SET @Result = ISDATE(@P_DateString)
    Return
    GO
    
    DECLARE @result int
    EXEC usp_valid_date '1/1/2001', @result OUTPUT
    SELECT @result
    EXEC usp_valid_date 'x/x/xxxx', @result OUTPUT
    SELECT @result
    GO
    
    DROP PROC usp_valid_date 
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Mar 2004
    Posts
    6
    Originally posted by Pat Phelan
    Have you tried something like:
    PHP Code:
    CREATE PROCEDURE dbo.d221
       
    @pcDate CHAR(8)
    AS

    SELECT IsDate(@pcDate)

    RETURN 
    This will return a result set with a BIT value of one if the date is valid, or zero if it is not. Note that you could just as easily concot the SELECT statement in your client code and bypass the procedure, but that is a matter of taste.

    -PatP
    I can use Concatenate Strings and write select statement but in my application to execute the sql statement it looks for a table name which i don't have. My problem is if i write a procedure by using Isdate function it is not working from application . i need to find out a different way of writing a procedure with out using Isdate

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can your application do the SELECT IsDate() by itself? What language/data access library are you using?

    -PatP

  10. #10
    Join Date
    Mar 2004
    Posts
    6
    hey if you are ok can you give your phone number , So that i can call you and explain

  11. #11
    Join Date
    Mar 2004
    Posts
    6
    Originally posted by tejasai
    hey if you are ok can you give your phone number , So that i can call you and explain
    My application can not use the select statement by itself. Iam usinf Sybase ECMap(translator tool for EDI)

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by tejasai
    My application can not use the select statement by itself. Iam usinf Sybase ECMap(translator tool for EDI)
    Ooooh. Bummer, dude!

    You might be able to use:
    PHP Code:
    CREATE PROCEDURE dbo.d221
       
    @pcDate CHAR(8)
    AS

    SELECT IsDate(@pcDateFROM dbo.sysobjects WHERE id Object_Id('sysobjects')

    RETURN 
    This might fake out the infernal beast and get you the results that you want.

    -PatP

Posting Permissions

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