CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
bala3569
bala3569

Reputation: 11000

Datetime comparison error in sql?

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.

Upvotes: 6

Views: 2623

Answers (2)

Tristen
Tristen

Reputation: 21

I just ran into this same problem when trying to make a Unix timestamp from a date,

Here's an example of what I was trying to do:

select DATEDIFF(second,'1970-01-01','2200-01-11');

It overflows since DATEDIFF is trying to return a signed integer - which can only hold just over 68 years worth of seconds.

In order to get the Unix timestamp (which I need so I can feed it into Sphinx Search), you can get the difference in minutes first, then cast the result as a big integer and then multiply by 60 seconds:

select CAST(DATEDIFF(minute,'1970-01-01','2200-01-11') AS BIGINT) * 60;

Now we should be able to handle dates that vary in difference of up to 4000 years or so. If you need even more room, simply change out minute with bigger and bigger intervals, and change the seconds multiplier accordingly.

Upvotes: 0

Derek Flenniken
Derek Flenniken

Reputation: 487

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

Upvotes: 1

Related Questions