Cast or Convert to Time?

Question

Hi,

I know this is probably my lack of knowledge, but here goes...

I have a string field which holds a time in 24 hour format (hhmm), I'm using substring to separate out the hours and the minutes, concatenating it (and adding separators and seconds) to the format hh:mm:ss. I've then tried wrapping this in 'CAST' ... 'AS TIME', the dataset returns 1/1/1970. I've tried the same using CONVERT, I get the same result. Could anyone help (without laughing!!).

Thanks.

Comments

Hi,

    please provide more details...

Which database are you using as datasource?

Which is the whole query you've arranged to get the result?

Best regards,

Francesco

---

Hi Francesco,

It's a progress database connected with the DataDirect JDBC driver... This is what I'm trying to do:

SELECT
  PUB.TimeSheet."Date",
  PUB.TimeSheet.StartTime,
  PUB.Timesheet.EndTime,
  CAST(SubString(PUB.TimeSheet.StartTime, 1, 2) + ':' + SubString(PUB.TimeSheet.StartTime, 3, 2) + ':00' AS TIME) AS
  TimeStart,
  CAST(SubString(PUB.TimeSheet.EndTime, 1, 2) + ':' + SubString(PUB.TimeSheet.EndTime, 3, 2) + ':00' AS TIME) AS
  TimeEnd,
  PUB.TimeSheet.WorkType,
  PUB.TimeSheet.Personnel_ID,
  PUB.TimeSheet.Problem_Id,
  PUB.Problem.IssueNo,
  PUB.Personnel."Name",
  PUB.Problem."Name" AS ProblemName,
  PUB.TimeSheet.Description
FROM
  PUB.TimeSheet
  LEFT OUTER JOIN PUB.Problem ON PUB.Problem."Id" = PUB.TimeSheet.Problem_Id
  INNER JOIN PUB.Personnel ON PUB.Personnel."ID" = PUB.TimeSheet.Personnel_ID
WHERE
  PUB.TimeSheet."Date" >= '2018-01-01' AND
  PUB.Personnel.fProgrammer > 0 AND
  PUB.Personnel."Level" > 0
ORDER BY
  PUB.TimeSheet."Date"

Thanks,

Rob.

Answer 1

Hi,

    depending on the datasource, a full support to time data may miss (Knowage usually manages timestamps or dates).

Furthermore, officially Knowage doesn't support Progress database, so you may be incurred in the latter situation.

I guess you can just continue to output strings, moving from "hhmm" to "hh:mm:00" format without casting to time.

We really appreciate your feedback and we'll plan to improve support to time data in next releases of Knowage.

Best regards,

Francesco