locked
Check for datetime2 if it is valid RRS feed

  • Question

  • User351619809 posted
    Hi All,

    I have a stored procedure like below:


    Create proc test1 ( @rep_date datetime2 ) AS if ISDATE(@rep_date ) <> 0 SET @rep_date = NULL


    I am getting an error right here saying  "Argument data type datetime2 is invalid for argument 1 of isdate function." I read in MSDN article if the date is of type datetime2 then a 0 will be returned.
     

    Monday, November 9, 2015 2:50 PM

Answers

  • User77042963 posted

    I can reproduce your error with your code but you don't need to use ISDATE function here since it was passed in as datetime2.

    if @rep_date  is not null
    	 SET @rep_date   = NULL
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 9, 2015 3:28 PM
  • User-62323503 posted

    Hi, 

    Datetime2 variable can hold only NULL or a valid date. Since @rep_date is datetime2, there is no need to check it with isdate. you can use  if @rep_date is null.

    Here, if the @rep_date variable has date, you are again setting it as null, it means in every case @rep_date would be null. Then I did n't understand the requirement of having this parameter...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 10, 2015 1:06 AM

All replies

  • User77042963 posted

    I can reproduce your error with your code but you don't need to use ISDATE function here since it was passed in as datetime2.

    if @rep_date  is not null
    	 SET @rep_date   = NULL
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 9, 2015 3:28 PM
  • User-1716253493 posted

    Hi, you don't need to check @rep_date, you can set defaultvalue to null

    Create proc test1
    (
      @rep_date  datetime2 = null
    )

    AFAIK, ISDATE is used to check chararacter string instead of datetime, for example

    if ISDATE('abcdefg') <> 0
    	 SET @rep_date = NULL
    if ISDATE('11/10/2015') <> 0
    	 SET @rep_date = NULL

    I guess @rep_date null already if don't pass a value
    You can't check @rep_date valid or not because i'ts already datetime type except @rep_date is character string

    Check @rep_date is null or not only

    Monday, November 9, 2015 8:18 PM
  • User-62323503 posted

    Hi, 

    Datetime2 variable can hold only NULL or a valid date. Since @rep_date is datetime2, there is no need to check it with isdate. you can use  if @rep_date is null.

    Here, if the @rep_date variable has date, you are again setting it as null, it means in every case @rep_date would be null. Then I did n't understand the requirement of having this parameter...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, November 10, 2015 1:06 AM
  • User364663285 posted

    Try

    Create proc test1
    (
      @rep_date  datetime
    )
    
    
    AS
    
    if ISDATE(@rep_date  ) <> 1
    	 SET @rep_date   = NULL

    which would produce null date when it's invalid.

    Tuesday, November 10, 2015 10:22 AM