November 22, 2005, 07:27 PM
Alan MainTime portion of datetime
There must be a simple solution to this, but I've tried everything I can find in the IBI docs and nothing is working.
I have a datetime field 'LogDate' in an SQL Server table where a typical value looks like:
10/18/2005 3:02:55 PM
I need to separate the date and time into two fields displayed in different columns, as well as reformat the date. The following command works fine:
DisplayDate/MtDYY = LogDate;
results in the desired Oct 18, 2005 being displayed.
I cannot however get the time by itself. I tried
DisplayTime/HhISA = LogDate;
plus I tried everything else that looked promising, such as DATECVT, HCNVRT, HNAME, EDIT; everything resulted in an error.
What to do?
November 23, 2005, 06:15 AM
ptpYou may be looking for HTIME ...?
HTIME (length, dtfield, 'Dformat')length
Is the length of the input date-time value. Valid values are:
8 for time values down to milliseconds.
10 for input time values down to microseconds.
dtfield
Is the date-time value to use for extracting the time. You can supply the name of a date-time field, a date-time constant, or an expression that returns a date-time value.
Dformat
Is the USAGE format of the returned number of milliseconds or microseconds, enclosed in single quotation marks.
now convert the output to time format.... DEFINE FILE CAR
DT1 /A20 = IF COUNTRY EQ 'ENGLAND' THEN '20000626054500000' ELSE '20000205033000000';
TRANSDATE /HYYMDS = HINPUT(14, DT1, 8, 'HYYMDS');
THETIME /D14.2 = HTIME(8, TRANSDATE, 'D14.2');
MYHRS /I2 = INT(THETIME / 60 / 60 / 1000);
MYMINS /I2 = INT(THETIME - (MYHRS * 60 * 60 * 1000)) / 60 / 1000;
MYSECS /I2 = INT(THETIME - (MYHRS * 60 * 60 * 1000) - (MYMINS * 60 * 1000) ) / 1000;
MYTIME /A9 = EDIT(MYHRS) | EDIT(MYMINS) | EDIT(MYSECS) | '000';
OUTTME /A8 = EDIT(MYTIME,'99:99:99');
END
TABLE FILE CAR
PRINT TRANSDATE
THETIME
OUTTME
BY COUNTRY NOPRINT
IF COUNTRY EQ 'ENGLAND' OR 'FRANCE'
END
Hope this helps,
Paul.
November 23, 2005, 10:17 AM
JimRiceHere's another option to add to Paul's example:
DEFINE FILE CAR
DT1/A20 = IF COUNTRY EQ 'ENGLAND' THEN '20000626054500000' ELSE '20000205033000000';
TRANSDATE/HYYMDS = HINPUT(14, DT1, 8, 'HYYMDS');
-* (THIS GIVES YOU ALPHA DATE/TIME)
TMP_DTS/A20 = HCNVRT(TRANSDATE,'(HYYMDS)',20,'A20');
-* (THIS GIVES YOU SMART DATE)
TMP_DATE/YYMD = HDATE(TRANSDATE,'YYMD');
-* (THIS GIVES YOU ALPHA TIME)
TMP_TIME/A8 = EDIT ((HCNVRT(TRANSDATE,'(HYYMDS)',20,'A20')),'$$$$$$$$$$$99999999$');
END
TABLE FILE CAR
PRINT
TRANSDATE
TMP_DTS
TMP_DATE
TMP_TIME
BY COUNTRY NOPRINT
IF COUNTRY EQ 'ENGLAND' OR 'FRANCE'
END
Jim
November 23, 2005, 07:40 PM
StanIf you happen to be on an older version of FOCUS, you can change the master file to reflect the entire date time stamp as an A19(or is it A17...I can't count right now), anyway, if you change it to Alpha, you could easily then split it into two separate parts. Just keep in mind that once you go down the Alpha route, it can be a bear to bring it back to representing time.
Just two cents worth from an older coder who used to have to do it prior to the really cool time functions we have now.
November 24, 2005, 03:30 AM
Tony AStan,
If I remember correctly, the equivalent alpha for an actual of DATE was A26. I can certainly remember having to use that format against DB2 in the early years!!