TIME String using Variable H (0 to 23) & M (0 to 59)

50 pts.
Tags:
DB2 10.1
Microsoft Access
Microsoft Access database
Working on project to convert a Microsoft Access database application to DB2 10.1. This my first with DB2. 1st I tried SQL Script SQL Script: INSERT INTO "IBNserv"."ChkMe"("Clt")>>> [Column Clt Data Type TIME NOT NULL DEFALUT 00:00:00] VALUES CAST('12:14:00' AS TIME) STATUS INSERT INTO "IBNserv"."ChkMe"("Clt") VALUES CAST('12.14.00' AS TIME) Updated 1 rows. Query execution time => 47 ms 2nd I tried SP CREATE PROCEDURE SP_Time () DYNAMIC RESULT SETS 1 P1: BEGIN DECLARE H SMALLINT;DECLARE M SMALLINT;DECLARE S SMALLINT; SET H=12;SET M=14;SET S=00; INSERT INTO "IBNserv"."ChkMe"("Clt") VALUES CAST('H:M:S' AS TIME); END P1 DB2ADMIN.SP_TIME - Deploy for debug started. DB2ADMIN.SP_TIME - Create stored procedure completed. DB2ADMIN.SP_TIME - Deploy for debug successful. 3rd I tried to Run SP & following code is the result. {CALL DB2ADMIN.SP_TIME(?)} The syntax of the string representation of a datetime value is incorrect.. SQLCODE=-180, SQLSTATE=22007, DRIVER=3.63.108 Run of routine failed. - Roll back completed successfully. What is the correct string representation of a TIME value? I want to use variable H (0 to 23) & Variable M (0 to 59) .Variable S will always be 0.
ASKED: January 12, 2013  10:37 AM
UPDATED: January 14, 2013  1:33 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    'H:M:S' wouldn't this attempt to cast the literal string 'H:M:S' as a time .. Should be something like this... I'm sure this isn't the correct format. Declare HMS as character Asuming H,M and S are character HMS = H + ':' +M + ':' + S then you can use CAST(HMS as Time)
    48,565 pointsBadges:
    report
  • TomLiotta
    Maybe [ '00:00:00' + :H hours + :M minutes + :S seconds ]? The '00:00:00' might need to be CAST() as a TIME value. -- Tom
    125,585 pointsBadges:
    report
  • Apcalogic
    Thanks a lot philp1jb! After 5 days constant tries, you have clinched the issue by showing the way out Thanks again & again .This is SP & with result sets. Thanks IT KE! apcalogic CREATE PROCEDURE SP_Time (IN H SMALLINT,M SMALLINT, OUT T TIME) DYNAMIC RESULT SETS 1 P1: BEGIN DECLARE HC CHAR(2);DECLARE MC CHAR(2);DECLARE TC CHAR(8); SET HC=CASE WHEN H BETWEEN 0 AND 9 THEN CAST('0'||H AS CHAR(2)) WHEN H BETWEEN 10 AND 23 THEN CAST(H AS CHAR(2)) ELSE CAST('00' AS CHAR(2)) END; SET MC=CASE WHEN M BETWEEN 0 AND 9 THEN CAST('0'||M AS CHAR(2)) WHEN M BETWEEN 10 AND 59 THEN CAST(M AS CHAR(2)) ELSE CAST('00' AS CHAR(2)) END; SET TC= HC||':'||MC||':'||'00'; SET T= CAST(TC AS TIME); END P1 Status: Run: DB2ADMIN.SP_TIME(SMALLINT, SMALLINT, TIME) {CALL DB2ADMIN.SP_TIME(?,?,?)} Run of routine completed successfully. Query execution time => 78 ms Parameters VALUE VALUE H >>INPUT>>SMALLINT>>12--------1 M>>INPUT>>SMALLINT>>14-------15 T>>OUTPUT>>TIME>>>>>>>>>>12:14:00-----01:15:00
    50 pointsBadges:
    report
  • TomLiotta
    Finally had a chance to test. This statement works as a query: SELECT (cast('00:00:00' as TIME) + 1 hour + 2 minutes + 3 seconds) FROM sysibm/sysdummy1 So you should be able to use an expression something like this: SET T= (cast('00:00:00' as TIME) + H hours + M minutes + S seconds) If you create it as a UDF, you could probably just return that expression as the function value. That should be about all there is to it. There might be an even shorter way to code it, but getting the '00:00:00' out of there seems difficult. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    Well I like Tom's solution.
    48,565 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following