DateDiff Function

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  May 18, 2010 at 6:21 am

  #235190

  Hello Everyone

  I am looking for a function that will use the return the differences in Years, Months, Days, Hours, Minutes, Seconds, and Milliseconds between a date selected and the getdate() function

  With colon separators between each

  000:00:000:00:00:00:000

  I am close, but still some things are not correct, like the number of hours, mine is returning 80 hours, and then nothing for the remaining values

  This is what I have so far, but I am certainly open for any suggestions

  DECLARE @LastUpdated datetime

  DECLARE @Today datetime

  DECLARE @Years int

  DECLARE @Months int

  DECLARE @Days int

  DECLARE @Hours int

  DECLARE @Minutes int

  DECLARE @Seconds int

  DECLARE @Milliseconds int

  SET @Today = GETDATE()

  SET @LastUpdated = '5/5/2010'

  SET @Years = DATEDIFF(yyyy, @LastUpdated, @Today)

  SET @Months = DATEDIFF(mm, @LastUpdated, @Today)

  SET @Days = DATEDIFF(dd, @LastUpdated, @Today)

  SET @Hours = DATEDIFF(hh, @LastUpdated, @Today)

  SET @Minutes = DATEDIFF(mi, @LastUpdated, @Today)

  SET @Seconds = DATEDIFF(ss, @LastUpdated, @Today)

  SET @Milliseconds = DATEDIFF(ms, @LastUpdated, @Today)

  SELECT

  @Years

  ,' : '

  ,@Months

  ,' : '

  ,@Days

  ,' : '

  ,@Hours

  ,' : '

  ,@Minutes

  ,' : '

  ,@Seconds

  ,' : '

  ,@Milliseconds

  SELECT CAST(@Years AS varchar(4) ) +':'+ CAST(@Months AS varchar(2) ) +':'+ CAST(@Days AS varchar(2)) +':'+ CAST(@Hours AS varchar(2) ) +':'+ CAST(@Minutes AS varchar(2) ) +':'+ CAST(@Seconds AS varchar(2) ) +':'+ CAST(@Milliseconds AS varchar(4))

  Thanks

  Andrew SQLDBA

• tommyh

  SSCertifiable

  Points: 6302

  More actions

  May 18, 2010 at 6:37 am

  #1168760

  You are in each case returning the total difference between @LastUpdated and @Today. So in the last ur getting ALOT of numbers and then trying to convert that to varchar(4). Hence you get '*' instead to indicate that the the value didnt fit.

  If you replace the SET part in the middle with

  --------------------------

  SET @Years = DATEDIFF(yyyy, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(yyyy, @Years, @LastUpdated)

  SET @Months = DATEDIFF(mm, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(mm, @Months, @LastUpdated)

  SET @Days = DATEDIFF(dd, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(dd, @Days, @LastUpdated)

  SET @Hours = DATEDIFF(hh, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(hh, @Hours, @LastUpdated)

  SET @Minutes = DATEDIFF(mi, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(mi, @Minutes, @LastUpdated)

  SET @Seconds = DATEDIFF(ss, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(ss, @Seconds, @LastUpdated)

  SET @Milliseconds = DATEDIFF(ms, @LastUpdated, @Today)

  --------------------------

  It should work better (if i understod you correctly what you where after)

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  May 18, 2010 at 6:41 am

  #1168768

  Andrew,

  Use the following; this will work out for you!

  SELECT

  STUFF('0000',LEN('0000')-LEN(CONVERT(VARCHAR,@Years))+1,LEN(@Years),@Years)

  Tell us if that worked!

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  May 18, 2010 at 7:53 am

  #1168824

  Hey Guys

  These are really close, but when I use an actual value from the database, I am getting a weird result.

  This works perfectly if the date value is '5/15/2010', but when the date value is this:

  '20091231 15:24:13.080' the result is not correct at all.

  Thanks for any help with this.

  DECLARE @LastUpdated datetime

  DECLARE @Today datetime

  DECLARE @Years int

  DECLARE @Months int

  DECLARE @Days int

  DECLARE @Hours int

  DECLARE @Minutes int

  DECLARE @Seconds int

  DECLARE @Milliseconds int

  SET @Today = GETDATE()

  SET @LastUpdated = '20091231 15:24:13.080'

  SET @Years = DATEDIFF(yyyy, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(yyyy, @Years, @LastUpdated)

  SET @Months = DATEDIFF(mm, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(mm, @Months, @LastUpdated)

  SET @Days = DATEDIFF(dd, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(dd, @Days, @LastUpdated)

  SET @Hours = DATEDIFF(hh, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(hh, @Hours, @LastUpdated)

  SET @Minutes = DATEDIFF(mi, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(mi, @Minutes, @LastUpdated)

  SET @Seconds = DATEDIFF(ss, @LastUpdated, @Today)

  SET @LastUpdated = DateAdd(ss, @Seconds, @LastUpdated)

  SET @Milliseconds = DATEDIFF(ms, @LastUpdated, @Today)

  SELECT CAST(@Years AS varchar(4) ) +':'+ CAST(@Months AS varchar(2) ) +':'+ CAST(@Days AS varchar(2)) +':'+ CAST(@Hours AS varchar(2) ) +':'+ CAST(@Minutes AS varchar(2) ) +':'+ CAST(@Seconds AS varchar(2) ) +':'+ CAST(@Milliseconds AS varchar(4))

• tommyh

  SSCertifiable

  Points: 6302

  More actions

  May 18, 2010 at 8:30 am

  #1168856

  Okay, how about this. Tried on your 2 dates and looks okayis to me. The MS i think is due to DateTime datatype.

  DECLARE @LastUpdated datetime

  DECLARE @Today datetime

  DECLARE @Years int

  DECLARE @Months int

  DECLARE @Days int

  DECLARE @Hours int

  DECLARE @Minutes int

  DECLARE @Seconds int

  DECLARE @Milliseconds int

  SET @Today = GETDATE()

  SET @LastUpdated = '2010-05-05'

  --set @LastUpdated = '20091231 15:24:13.080'

  SET @Years = DATEDIFF(yyyy, @LastUpdated, @Today)

  if @Years > 0 and DatePart(mm, @LastUpdated) > DatePart(mm, @Today) SET @Years = @Years - 1

  SET @LastUpdated = DateAdd(yyyy, @Years, @LastUpdated)

  SET @Months = DATEDIFF(mm, @LastUpdated, @Today)

  if @Months > 0 and DatePart(dd, @LastUpdated) > DatePart(dd, @Today) SET @Months = @Months - 1

  SET @LastUpdated = DateAdd(mm, @Months, @LastUpdated)

  SET @Days = DATEDIFF(dd, @LastUpdated, @Today)

  if @Days > 0 and DatePart(hh, @LastUpdated) > DatePart(hh, @Today) SET @Days = @Days - 1

  SET @LastUpdated = DateAdd(dd, @Days, @LastUpdated)

  SET @Hours = DATEDIFF(hh, @LastUpdated, @Today)

  if @Hours > 0 and DatePart(mi, @LastUpdated) > DatePart(mi, @Today) SET @Hours = @Hours - 1

  SET @LastUpdated = DateAdd(hh, @Hours, @LastUpdated)

  SET @Minutes = DATEDIFF(mi, @LastUpdated, @Today)

  if @Minutes > 0 and DatePart(ss, @LastUpdated) > DatePart(ss, @Today) SET @Minutes = @Minutes - 1

  SET @LastUpdated = DateAdd(mi, @Minutes, @LastUpdated)

  SET @Seconds = DATEDIFF(ss, @LastUpdated, @Today)

  if @Seconds > 0 and DatePart(ms, @LastUpdated) > DatePart(ms, @Today) SET @Seconds = @Seconds - 1

  SET @LastUpdated = DateAdd(ss, @Seconds, @LastUpdated)

  SET @Milliseconds = DATEDIFF(ms, @LastUpdated, @Today)

  SELECT

  @Years

  , ' : '

  , @Months

  , ' : '

  , @Days

  , ' : '

  , @Hours

  , ' : '

  , @Minutes

  , ' : '

  , @Seconds

  , ' : '

  , @Milliseconds

  SELECT CAST(@Years AS varchar(4) ) +':'+ CAST(@Months AS varchar(2) ) +':'+ CAST(@Days AS varchar(2)) +':'+ CAST(@Hours AS varchar(2) ) +':'+ CAST(@Minutes AS varchar(2) ) +':'+ CAST(@Seconds AS varchar(2) ) +':'+ CAST(@Milliseconds AS varchar(4))

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  May 18, 2010 at 8:48 am

  #1168874

  Oh Yes, that is perfect, that gives exactly what I expect.

  Thank you very much for your help, and to everyone

  Thank you

  Andrew SQLDBA

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  May 19, 2010 at 9:26 pm

  #1169846

  I'm not sure why you need 3 digits for days since a month can't have more than 31 days in a month, but here's my humble take on the problem... T-SQL does most of the math for me here...

  DECLARE @LastUpdated DATETIME;

  SELECT @LastUpdated = '20091231 15:24:13.080';

  SELECT STUFF(

  STUFF(

  REPLACE(REPLACE(REPLACE(CONVERT(CHAR(23),GETDATE() - @LastUpdated,121),'.',':'),' ',':'),'-',':')

  ,1,4,REPLACE(STR(DATEPART(yy,GETDATE() - @LastUpdated)-1900,3),' ',0))

  ,8,0,'0');

  Just don't try such simple date subtraction with the DATE, TIME, or DATETIME2 datatypes when you get to 2k8. MS continues to remove useful techniques. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• tommyh

  SSCertifiable

  Points: 6302

  More actions

  May 19, 2010 at 11:56 pm

  #1169869

  Jeff Moden (5/19/2010)

  ---

  I'm not sure why you need 3 digits for days since a month can't have more than 31 days in a month, but here's my humble take on the problem... T-SQL does most of the math for me here...

  DECLARE @LastUpdated DATETIME;

  SELECT @LastUpdated = '20091231 15:24:13.080';

  SELECT STUFF(

  STUFF(

  REPLACE(REPLACE(REPLACE(CONVERT(CHAR(23),GETDATE() - @LastUpdated,121),'.',':'),' ',':'),'-',':')

  ,1,4,REPLACE(STR(DATEPART(yy,GETDATE() - @LastUpdated)-1900,3),' ',0))

  ,8,0,'0');

  Just don't try such simple date subtraction with the DATE, TIME, or DATETIME2 datatypes when you get to 2k8. MS continues to remove useful techniques. 😉

  Well its a matter off what one consider is the "right" answer

  With Today = '2010-05-20 07:28:30.100' (instead of a getdate())

  On date: 2010-05-05

  Yours: 000:01:016:07:28:30:100

  Mine: 0:0:15:7:28:30:100

  On date: '20091231 15:24:13.080'

  Yours: 000:05:020:16:04:17:020

  Mine: 0:4:19:16:4:17:20 (a zero here before the 20 would be nice... details 🙂 )

  Being me... i think mine is right 😀

• Jeff Moden

  SSC Guru

  Points: 1004432

  More actions

  May 20, 2010 at 7:45 am

  #1170131

  Heh... dang it... I forgot to subtract a month and a day. Thanks for the feedback... I'll fix the error tonight after work.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)