时间/日期类型

日期/时间类型有timestamp 、date、time 、interval ；下面以timestamp 、date、time 举例说明。

插入数据

    SQLHENV      hEnv    = SQL_NULL_HENV;
    SQLHDBC      hDbc    = SQL_NULL_HDBC;
    SQLHSTMT     hStmt   = SQL_NULL_HSTMT;
    SQLRETURN    rc      = SQL_SUCCESS;
    SQLINTEGER   RETCODE = 0;

    TIMESTAMP_STRUCT sTime, sTimeT ;
    DATE_STRUCT dTime, dTimeT ;
    TIME_STRUCT tTime, tTimeT ;
    SQLLEN cbID = 0;
    SQLCHAR result[200]={0};

    rc = SQLExecDirect(hStmt,(SQLCHAR*)"drop table IF EXISTS test_timestamp_date_time",SQL_NTS);
    if (!SQL_SUCCEEDED(rc))
        goto exit;
        
    rc = SQLExecDirect(hStmt,(SQLCHAR*)"create table test_timestamp_date_time(tmsp timestamp, dt date, tm time)",SQL_NTS);
    if (!SQL_SUCCEEDED(rc))
        goto exit;

    rc = SQLExecDirect(hStmt,(SQLCHAR*)"insert into test_timestamp_date_time values('2012-12-14 12:12','2013-11-15', '04:12:03')",SQL_NTS);
    if (!SQL_SUCCEEDED(rc))
        goto exit;

    SQLPrepare(hStmt,(SQLCHAR*)"insert into test_timestamp_date_time values(?, ?, ?)",SQL_NTS);

    sTime.year = 1992;
    sTime.month = 12;
    sTime.day = 12;
    sTime.hour = 9;
    sTime.minute = 10;
    sTime.second = 31;
    SQLBindParameter(hStmt,1,SQL_PARAM_INPUT,SQL_C_TYPE_TIMESTAMP,SQL_TYPE_TIMESTAMP,100,0,(SQLPOINTER)&sTime,sizeof(TIMESTAMP_STRUCT),&cbID);

    dTime.year = 1982;
    dTime.month = 12;
    dTime.day = 11;
    SQLBindParameter(hStmt,2,SQL_PARAM_INPUT,SQL_C_TYPE_DATE ,SQL_TYPE_DATE ,100,0,(SQLPOINTER)&dTime,sizeof(DATE_STRUCT),&cbID);

    tTime.hour = 10;
    tTime.minute = 10;
    tTime.second = 22;
    SQLBindParameter(hStmt,3,SQL_PARAM_INPUT,SQL_C_TYPE_TIME ,SQL_TYPE_TIME ,100,0,(SQLPOINTER)&tTime,sizeof(TIME_STRUCT),&cbID);

    rc = SQLExecute(hStmt);
    if ((!SQL_SUCCEEDED(rc)))
            goto exit;

读取数据

	rc = SQLExecDirect(hStmt,(SQLCHAR*)"select * from test_timestamp_date_time;",SQL_NTS);
    if (!SQL_SUCCEEDED(rc))
        goto exit;

    rc = SQLFetch(hStmt);
    while(rc != SQL_NO_DATA)
    {
        /* test SQLGetData */
        SQLGetData(hStmt,1,SQL_C_TYPE_TIMESTAMP,(SQLPOINTER)&sTimeT,sizeof(sTimeT),&cbID);
        sprintf((char*)result, "%.4d-%.2d-%.2d %.2d:%.2d:%.2d", sTimeT.year, sTimeT.month, sTimeT.day,sTimeT.hour,sTimeT.minute,sTimeT.second);
        printf("TIMESTAMP = %s\n\n",result);

        SQLGetData(hStmt,2,SQL_C_TYPE_DATE ,(SQLPOINTER)&dTimeT,sizeof(dTimeT),&cbID);
        sprintf((char*)result, "%.4d-%.2d-%.2d", dTimeT.year, dTimeT.month, dTimeT.day);
        printf("DATE = %s\n\n",result);

        SQLGetData(hStmt,3,SQL_C_TYPE_TIME ,(SQLPOINTER)&tTimeT,sizeof(tTimeT),&cbID);
        sprintf((char*)result, "%.2d:%.2d:%.2d", tTimeT.hour,tTimeT.minute,tTimeT.second);
        printf("TIME = %s\n\n",result);

        rc = SQLFetch(hStmt);  
    };

    if (!SQL_SUCCEEDED(rc) && rc != SQL_NO_DATA)
        goto exit;

VastbaseG100

时间/日期类型

弹框头部