Applies To:
  • CitectSCADA
Summary:
The customer required the data storage facility within an SQL server. We want to transfer the energy consumed per month with a particular date. The problem we are facing is that the all the other data is transferred properly except the Date. Date is stored in the wrong way. The sample code is shown below;

STRING DATE2 =Date(2);
SQLExec(hSQL, " INSERT INTO COST VALUES(" + DATE2 + "," + OCC_NO + "," + LDB + "," + USDB + "," + ESDB +"," + COMMON + "," + AHU + "," + SERVER +"," + WATER + "," + TOTAL + ")");

Can you suggest the format for how to transfer the date to the SQL server?

 

Solution:
The date needs to be massaged into a format that the SQL Server will accept. We need a function that will return the date in a way that the server will relate to. Try this general cicode function to correctly format the date variable and then pass it to the SQL insert.

String
Function SQLTime()
    String sTime;
    Int iTime;
    iTime = TimeCurrent()
    sTime = PadNum(DateDay(iTime), 2) + " " + MonthStr(DateMonth(iTime))+ " " + IntToStr(DateYear(iTime,1));
    sTime = sTime + " " + PadNum(TimeHour(iTime),2) + ":" + PadNum(TimeMin(iTime),2) + ":" +PadNum(TimeSec(iTime),2)
    Return sTime;
END

String
Function MonthStr(Int iMonth)
    Select Case iMonth
        Case 1
            Return "Jan"
        Case 2
            Return "Feb"
        Case 3
            Return "Mar"
        Case 4
            Return "Apr"
        Case 5
            Return "May"
        Case 6
            Return "Jun"
        Case 7
            Return "Jul"
        Case 8
            Return "Aug"
        Case 9
            Return "Sep"
        Case 10
            Return "Oct"
        Case 11
            Return "Nov"
        Case 12
            Return "Dec"
        End Select
END

String
Function PadNum(Int iValue, Int iDigits)
    Return StrPad(IntToStr(iValue), "0", -iDigits);
END

 
Keywords:
 

Attachments