Post reply

DateDiff Function

  • 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

  • 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)

  • 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!

  • 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))

  • 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))

  • 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

  • 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)

  • 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 😀

  • 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)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy