Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2011
    Posts
    21

    Unanswered: Concat + time_format???

    hi,

    i need to concat two columns aswell as perform a time_format on them, struggling with syntax, heres what i have:

    SELECT CONCAT(stf.forename, ' ', stf.surname) AS Name, DATE_FORMAT(shf.shift_date,'%b %d %Y') as Date,
    TIME_FORMAT(CONCAT(shf.start_time, '-', shf.end_time, '%H - %i %p')) as Shift
    FROM staff stf, shift shf, staff_shift ss
    WHERE shf.shiftID = ss.shiftID

    im getting the error

    "Incorrect parameter count in the call to native function 'TIME_FORMAT'"

    returned

    please help!!!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Have a look at your second TIME_FORMAT function:

    SELECT CONCAT(stf.forename, ' ', stf.surname) AS Name, DATE_FORMAT(shf.shift_date,'%b %d %Y') as Date,
    TIME_FORMAT(CONCAT(shf.start_time, '-', shf.end_time), '%H - %i %p') as Shift
    FROM staff stf, shift shf, staff_shift ss
    WHERE shf.shiftID = ss.shiftID
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2011
    Posts
    21
    thanks for the reply!

    i've got a result, which is great however the two 'times' are failing to concatenate in the column

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what datatypes are the start_time and end_time columns? could we see sample values?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2011
    Posts
    21
    the 'start_time and end_time are both 'TIME' datatypes, example entries would be 09:00:00 and 21:00:00

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, i see

    could you please explain what you were trying to do here...

    Code:
    TIME_FORMAT(CONCAT(shf.start_time, '-', shf.end_time), '%H - %i %p') as Shift
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2011
    Posts
    21
    the two columns need to be concatenated, its to display a shift for someone. Rather than have it displayed as 'start_time 09:00:00' as one column and 'finish_time 21:00:00' as another, i thought it would be be better for the end user user to be able to read it as just 'Shift 9:00 am - 9:00pm' (24 hour format is a possible alternative)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i understand

    try this --
    Code:
    SELECT CONCAT(stf.forename,' ',stf.surname) AS Name
         , DATE_FORMAT(shf.shift_date,'%b %d %Y') as Date
         , CONCAT(TIME_FORMAT(shf.start_time,'%h:%i %p')
                 ,' - '
                 ,TIME_FORMAT(shf.end_time),'%h:%i %p')
                 ) AS Shift
      FROM ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2011
    Posts
    21
    r937, ever a legend!! it works! thank you so much!!

    just to tie it up there was one bracket missing..

    SELECT CONCAT(stf.forename,' ',stf.surname) AS Name
    , DATE_FORMAT(shf.shift_date,'%b %d %Y') as Date
    , CONCAT(TIME_FORMAT(shf.start_time,'%h:%i %p')
    ,' - '
    ,TIME_FORMAT((shf.end_time),'%h:%i %p')
    ) AS Shift

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •