Datetime comparison error in sql?

Question

I have used date and time validation for scheduling a report...I have to schedule that reports for future date and time only and not previous date and time..I have used this

    declare @Dt varchar(50) 
    declare @Hr varchar(50)
    declare @trandate_time_tmp as TIME(0)

    select @trandate_time_tmp = getdate()
    set @Dt = DATEDIFF (D,@schedule_date ,@trandate_tmp )
    set @Hr = DATEDIFF (S,@schedule_date ,@trandate_time_tmp )

    if ( @Dt > 0)
    begin
        raiserror('Schedule Date should not be earlier than system date',16,1)
        return
    end

    if ( @Hr > 0) 
    begin
        raiserror('Schedule Time should not be earlier than system time',16,1)
        return
    end

For date part it is checking correctly but for time it is throwing error as

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Answer 1

Not exactly answering your question, but perhaps a solution to your problem. You don't need to use DATEDIFF and check the results, you could just compare the two dates.

IF ( @schedule_date <= GETDATE() )
BEGIN
  RAISERROR('Schedule date should not be earlier than system date', 16, 1)
  RETURN
END