Post reply

format field

  • October 29, 2015 at 9:46 am

    #304639

    Hello,

    i work with sql server 2008 and i have (for a sample) a table with two column (dateMeeting (datetime), hoursMeeting (int), minuteMeeting (int)

    i'm not the responsible about this design 🙂

    Here is it the sample table with some data

    declare @tbl table (rowid int, dateMeeting datetime, hoursMeeting int, minuteMeeting int)

    INSERT INTO @tbl values(1, '20151025', 12,0), (2,'20151110', 15, 1), (3, '20151113', 18,10)

    SELECT * FROM @tbl

    I would like the result as this :

    1 2015 10 25 12H00

    2 2015 11 10 15H01

    3 2015 11 13 18H10

    My question is how to format hoursMeeting and minuteMeeting in a single field ?

    Is there a function in sql server 2008 ?

    I can do a case but i think there are perhpas some other solution ..

    thanks

    christophe

  • October 29, 2015 at 9:53 am

    #1836437

    Would be a string concatination due to needing the H in the middle.

    Something like

    select

    rowid,

    datemeeting,

    RIGHT('00'+convert(varchar(2),hoursmeeting),2) +'H'+ RIGHT('00'+convert(varchar(2),minutemeeting),2)

    from @tbl

  • October 29, 2015 at 10:10 am

    #1836445

    A different way to do it.

    SELECT rowid,

    YEAR( dateMeeting),

    MONTH( dateMeeting),

    DAY( dateMeeting),

    STUFF( (hoursMeeting * 100) + minuteMeeting, 3, 0, 'H')

    FROM @tbl

    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

  • October 30, 2015 at 2:04 am

    #1836590

    Hello Luis, Anthony,

    thanks for your time and your sample that's exactly what I want ..

    yesterday before I leave my office I've begin a query with a case when column = 0 then '00' ...and so on ...

    but I'd prefer to ask a question and find the right way .. 🙂

    thanks

    christophe

Viewing 4 posts - 1 through 3 (of 3 total)

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