Skip to content

DF_FIELD_TIME

The time portion for a DateTime column.

Level

Column

Supported by

The DataFlex SQL Drivers (SQL Server, DB2, and ODBC)

Type

String, temporary

Access

Read / Write

Values

A string in the format hh:mm:ss

Syntax

Use cli.pkg
Get_Attribute DF_FIELD_TIME of {tableNumber} {columnNumber} to {StringVariable}
Set_Attribute DF_FIELD_TIME of {tableNumber} {columnNumber} to {StringVariable}

Remarks

This attribute can be used to get or set the time portion of a DateTime type column. This attribute is more flexible than the DF_FIELD_STORE_TIME attribute. You can set or get this attribute at any time, while the DF_FIELD_STORE_TIME is only applied when saving. The string is in the format hh:mm:ss.

This attribute can only be set outside of a Structure_Start ... Structure_End operation.

Example: CreateClockTable Procedure

Procedure CreateClockTable
    Handle hTable
    Handle hoWorkspace
    String sPath
    String sOrigFolder
    Integer iColumn
    Integer iIndex

    // Make sure int file comes in first folder of datapath by making that folder current
    Get phoWorkspace Of ghoApplication To hoWorkspace
    Get psDataPath Of hoWorkspace To sPath
    Get PathAtIndex Of hoWorkspace sPath 1 To sPath
    Get_Current_Directory To sOrigFolder
    Set_Directory sPath

    // Create a SQL Server table to store clock in/out times of employees
    Move 0 To hTable
    Structure_Start hTable "MSSQLDRV"
    Set_Attribute DF_FILE_PHYSICAL_NAME Of hTable To "Clock.int"
    Set_Attribute DF_FILE_RECNUM_TABLE Of hTable To False
    Set_Attribute DF_FILE_LOGIN Of hTable To "SERVER=(local);Trusted_Connection=yes;DATABASE=Northwind"
    Set_Attribute DF_FILE_TABLE_NAME Of hTable To "Clock"
    Set_Attribute DF_FILE_USE_DUMMY_ZERO_DATE Of hTable To True

    Move 0 To iColumn
    Create_Field hTable At iColumn
    Set_Attribute DF_FIELD_NAME Of hTable iColumn To "EmployeeID"
    Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_BCD
    Set_Attribute DF_FIELD_LENGTH Of hTable iColumn To 10
    Set_Attribute DF_FIELD_NATIVE_TYPE Of hTable iColumn To SQL_INTEGER

    Move 0 To iColumn
    Create_Field hTable At iColumn
    Set_Attribute DF_FIELD_NAME Of hTable iColumn To "ClockInTime"
    Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_DATE

    Move 0 To iColumn
    Create_Field hTable At iColumn
    Set_Attribute DF_FIELD_NAME Of hTable iColumn To "ClockOutTime"
    Set_Attribute DF_FIELD_TYPE Of hTable iColumn To DF_DATE

    Move 0 To iIndex
    Create_Index hTable At iIndex
    Set_Attribute DF_INDEX_NUMBER_SEGMENTS Of hTable iIndex To 3
    Set_Attribute DF_INDEX_SEGMENT_FIELD Of hTable iIndex 1 To 1
    Set_Attribute DF_INDEX_SEGMENT_FIELD Of hTable iIndex 2 To 2
    Set_Attribute DF_INDEX_SEGMENT_DIRECTION Of hTable iIndex 2 To DF_DESCENDING
    Set_Attribute DF_INDEX_SEGMENT_FIELD Of hTable iIndex 3 To 3
    Set_Attribute DF_FILE_PRIMARY_INDEX Of hTable To iIndex
    Set_Attribute DF_INDEX_NAME Of hTable iIndex To "ClockPK"

    Structure_End hTable

    // Reset current working folder to original value
    Set_Directory sOrigFolder

    // Add to filelist and generate fd
    Move 0 To hTable
    Get_Attribute DF_FILE_NEXT_EMPTY Of hTable To hTable
    If (hTable > 0) Begin
        Set_Attribute DF_FILE_ROOT_NAME Of hTable To "MSSQLDRV:Clock"
        Set_Attribute DF_FILE_DISPLAY_NAME Of hTable To "Clock sample table"
        Set_Attribute DF_FILE_LOGICAL_NAME Of hTable To "Clock"
        Open hTable
        Get psDDSRCPath Of hoWorkspace To sPath
        Get PathAtIndex Of hoWorkspace sPath 1 To sPath
        If (Right(sPath, 1) <> Sysconf(Sysconf_Dir_Separator)) ;
            Move (sPath - Sysconf(Sysconf_Dir_Separator)) To sPath
        Move (sPath - "Clock.fd") To sPath
        Output_Aux_File DF_AUX_FILE_FD For hTable To sPath
        Close hTable
    End
End_Procedure // CreateClockTable

The procedure above creates a new table in the SQL Server Northwind database. It is used to store clock in and clock out information of employees. Note that the table creates two columns of type date, but since SQL Server does not support the date type, these will be created as DateTime columns in SQL Server.

Example: ClockIn Procedure

Procedure ClockIn Integer iEmployee
    DateTime dtNow
    Begin_Transaction
    Clear Clock
    Move iEmployee To Clock.Employeeid
    Move (CurrentDateTime()) To dtNow
    Move (Left(dtNow, Pos(" ", dtNow) - 1)) To Clock.ClockInTime
    Set_Attribute DF_FIELD_TIME Of Clock.File_Number 2 To (Right(dtNow, Length(dtNow) - Pos(" ", dtNow)))
    SaveRecord Clock
    End_Transaction
End_Procedure // ClockIn

Example: ClockOut Procedure

Procedure ClockOut Integer iEmployee
    DateTime dtNow
    Boolean bFound
    Clear Clock
    Move iEmployee To Clock.Employeeid
    Fill_Field Clock.File_number 2 With DF_HIGH
    Find Gt Clock By 1
    Move (Found) To bFound
    If (bFound) Begin
        Begin_Transaction
        Move (CurrentDateTime()) To dtNow
        Move (Left(dtNow, Pos(" ", dtNow) - 1)) To Clock.ClockOutTime
        Set_Attribute DF_FIELD_TIME Of Clock.File_Number 3 To (Right(dtNow, Length(dtNow) - Pos(" ", dtNow)))
        SaveRecord Clock
        End_Transaction
    End
End_Procedure // ClockOut

The sample procedures above use the DF_FIELD_TIME attribute to store information in a Clock record.

Example: ShowTime Procedure

Procedure ShowTime
    Boolean bFound
    DateTime dtIn
    DateTime dtOut
    String sTime
    Clear Clock
    Find Gt Clock By 1
    Move (Found) To bFound
    While (bFound)
        If (Clock.ClockInTime <> 0 And Clock.ClockOutTime <> 0) Begin
            Get_Attribute DF_FIELD_TIME Of Clock.File_Number 2 To sTime
            If (Pos(".", sTime) > 0) ;
                Move (Left(sTime, Pos(".", sTime) - 1)) To sTime
            Move (String(Clock.ClockInTime) * sTime) To dtIn

            Get_Attribute DF_FIELD_TIME Of Clock.File_Number 3 To sTime
            If (Pos(".", sTime) > 0) ;
                Move (Left(sTime, Pos(".", sTime) - 1)) To sTime
            Move (String(Clock.ClockOutTime) * sTime) To dtOut

            Showln Clock.Employeeid " - " dtIn "  " dtOut " - " (dtOut - dtIn)
        End
        Find Gt Clock By 1
        Move (Found) To bFound
    End
End_Procedure // ShowTime

In the sample procedure above, the contents of the Clock table are listed. Note that in order to be able to move the time portion to a DataFlex DateTime variable, the fraction of the time must be removed. In SQL Server, DateTime columns are stored using fractions of seconds; the DataFlex DateTime variable does not support time fractions.