Post reply

concatenation of two columns as single

  • November 18, 2015 at 10:59 pm

    #305167

    Hi,

    How can you covert two integer columns into date and time then concatenate I to single column.

    In job history table, I want to show run_date and run_time into a single column. And show the run_ time as more readable clearly 20hr 11 min..

    Thanks

  • November 19, 2015 at 2:16 am

    #1840898

    How I have got around the problem in the past.

    ;with cte as

    (select

    convert(varchar,run_date) as rundate,

    right('000000'+convert(varchar,run_time),6) as runtime

    from msdb.dbo.sysjobhistory

    )

    select convert(datetime,rundate+' '+left(runtime,2)+':'+left(right(runtime,4),2)+':'+right(runtime,2))

    from cte

  • November 19, 2015 at 2:19 am

    #1840899

    Is this what your looking for?

    SELECTdbo.agent_datetime(run_date, run_time)

    FROMsysjobhistory;

    Robert van den Berg

    Freelance DBA
    Author of:

  • November 19, 2015 at 2:21 am

    #1840901

    Robert vd Berg (11/19/2015)

    Is this what your looking for?

    SELECTdbo.agent_datetime(run_date, run_time)

    FROMsysjobhistory;

    +1, learned something new with that, didn't even know that function existed.

  • November 19, 2015 at 3:01 am

    #1840911

    I only learned about this function after I spent numerous frustrating hours casting, concatenating, adding quotes, troubleshooting & starting over. Oh well, you live and learn...

    Robert van den Berg

    Freelance DBA
    Author of:

  • November 19, 2015 at 7:32 am

    #1840984

    I know this might seem like more work, but it should be faster than using the scalar function, which is basically a udf.

    SELECT DATETIMEFROMPARTS(

    run_date / 10000, --Year

    (run_date % 10000)/100, --Month

    run_date % 100, --Day

    run_time / 10000, --Hours

    (run_time % 10000)/100, --Minutes

    run_time % 100, --Seconds

    0) --Milliseconds

    FROM msdb.dbo.sysjobhistory;

    Of course, you could create your own inline tabl-valued function.

    CREATE FUNCTION iagent_datetime(@date int, @time int)

    RETURNS TABLE WITH SCHEMABINDING

    AS

    RETURN

    SELECT DATETIMEFROMPARTS(

    @date / 10000,

    (@date % 10000)/100,

    @date % 100,

    @time / 10000,

    (@time % 10000)/100,

    @time % 100,

    0) agent_real_datetime

    GO

    And yes, the original code for dbo.agent_datetime is inefficient.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 19, 2015 at 9:13 am

    #1841047

    Robert vd Berg (11/19/2015)

    Is this what your looking for?

    SELECTdbo.agent_datetime(run_date, run_time)

    FROMsysjobhistory;

    You beat me to it. It's not the epitome of efficiency but it's usually good enough. Luis' "iSF" would work much faster for large volumes.

    --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)
    Intro to Tally Tables and Functions

  • November 19, 2015 at 9:42 am

    #1841059

    Hi,

    Thanks for the reply.

    I use

    dateadd(hour, (run_time / 1000000) % 100,

    dateadd(minute, (run_time / 10000) % 100,

    dateadd(second, (run_time / 100) % 100,

    dateadd(millisecond, (run_time % 100) * 10, cast('00:00:00' as time(2))))))

    from sysjobhistory

    The result is coming something

    00:14:49:29

    00:23:00:00

    00:09:25:00

    But it is not correct. It has to come like this

    14:49:29:00

    23:00:00:00

    09:25:00:00

  • November 19, 2015 at 9:45 am

    #1841062

    I need same format as Anthony provided but without CTE because I already have several sub queries in my main query

  • November 19, 2015 at 9:58 am

    #1841068

    You have the wrong formulas to calculate each part. Check my previous post for correct formulas.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 19, 2015 at 10:48 am

    #1841095

    Thank you.

  • November 19, 2015 at 10:50 am

    #1841096

    Cotto vs Canelo Live Stream Tv Telecast

    https://www.facebook.com/Miguel-Cotto-vs-Canelo-Alvarez-Live-Stream-PPV-Boxing-1503111929983691/

    Cotto vs Canelo Live Stream[/url]

    http://watchcollegefootballgame.com/

    http://www.nflonlinegame.com/

Viewing 12 posts - 1 through 11 (of 11 total)

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