Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

about select datediff(s,lastupdate,getdate()) from tcompanysupplier error!

hi,experts
  in mytable tcompanysupplier the lastupdate column's value is 1900-01-01 00:00:00.000
when
 select datediff(s,lastupdate,getdate()) from tcompanysupplier
will show error,
because the result is bigint ,but the datediff function return int ,so will show error
how can i do it .i want write a self define function to do this,but i can not write ,can experts help me!

Best Regard!
0
hgbdelphi
Asked:
hgbdelphi
1 Solution
 
aprestoCommented:
what datatype is lastupdate? is it a datetime?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi hgbdelphi,
Its because datediff function returns an  int value.
FROM BOL
DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.

Cheers!
0
 
Mr_PeerapolCommented:
Create an user-defined function using code like this:

DECLARE @SecondDiff BIGINT
DECLARE @LastUpdate DATETIME
DECLARE @CurrentDate DATETIME

SET @SecondDiff = (CONVERT(BIGINT, DATEDIFF(d, @LastUpdate, @CurrentDate)) * 24 * 60 * 60) + DATEDIFF(s, DATEADD(d, DATEDIFF(d, @LastUpdate, @CurrentDate), @LastUpdate), @CurrentDate)

You will need to pass the current date to the user-defined function because GETDATE() cannot be used inside an user-defined function

0
 
hgbdelphiAuthor Commented:
hi,Mr_Peerapol experts,
thanks for your help,it's work fine,

create function j_datediff(@LastUpdate DATETIME , @CurrentDate DATETIME )
returns bigint
as
begin
        declare @SecondDiff bigint
      SET @SecondDiff = (CONVERT(BIGINT, DATEDIFF(d, @LastUpdate, @CurrentDate)) * 24 * 60 * 60) + DATEDIFF(s, DATEADD(d, DATEDIFF(d, @LastUpdate, @CurrentDate), @LastUpdate), @CurrentDate)
        return @SecondDiff
End
go
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now