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.