Hello all,
I need to find out how to send DateTime info as part of a MS SQL query to INSERT a new record into a table that contains a [datetime] type column. We are currently sending a string hoping the conversion will happen on the MS SQL side of things. The system we're testing this on has been upgraded from citect scada 7.3 to 2018 R2. I have attached the Cicode function we are using to test this transaction as well as a table creation file from MS SQL.
FUNCTION TestInsert() STRING sYear, sMonth, sDay, sTime, sHour, sMinute, sSecond; sManOvrd = "Manual Override"; st26 = "'TEST INSERT'"; st1=MachNumbDSK; st2=LotNoDSK; //st3=Date(10); sYear = StrPad(IntToStr(DateYear(TimeCurrent(),1)),"0",-4); sMonth = StrPad(IntToStr(DateMonth(TimeCurrent())),"0",-2); sDay = StrPad(IntToStr(DateDay(TimeCurrent())),"0",-2); sHour = StrPad(IntToStr(TimeHour(TimeCurrent())),"0",-2); sMinute = StrPad(IntToStr(TimeMin(TimeCurrent())),"0",-2); sSecond = StrPad(IntToStr(TimeSec(TimeCurrent())),"0",-2); st3= "'"+sYear+"-"+sMonth+"-"+sDay+"T"+sHour+":"+sMinute+":"+sSecond+"'"; //st3="GETDATE()"; Message("Date",st3,64); sUserName = vsUserName; st4=RealToStr(4.4,7,3); st5=RealToStr(5.5,7,3); st6=RealToStr(6.6,7,3); st7=RealToStr(7.7,7,3); st8=RealToStr(8.8,7,3); st9=RealToStr(9.9,7,3); st10=RealToStr(10.0,7,3); st11=RealToStr(11.1,7,3); st12=RealToStr(12.2,7,3); st13=RealToStr(13.3,7,3); st14=RealToStr(14.4,7,3); st15=RealToStr(15.5,7,3); st16=RealToStr(16.6,7,3); st17=RealToStr(17.7,7,3); st18=RealToStr(18.8,7,3); st19=RealToStr(19.9,7,3); st20=RealToStr(20.0,7,3); st21=RealToStr(21.1,7,3); st22=RealToStr(22.2,7,3); st23=RealToStr(23.3,7,3); st24=RealToStr(24.4,7,3); st25=RealToStr(25.5,7,3); st27=RealToStr(27.7,7,3); st28="111-222"; st29=Sublots1DSK; st30=Sublots2DSK; sqlAppStr1 = " ('"+st1+"','"+st2+"',"+st3+",'"+sUserName+"','"+sManOvrd+"',"+st4+","+st5+","+st6+","+st7+","+st8+","+st9+","+st10+","+st11+","+st12+","+st13+","+st14+"," sqlAppStr2 = st15+","+st16+","+st17+","+st18+","+st19+","+st20+","+st21+","+st22+","+st23+","+st24+","+st25+","+st26+","+st27+",'"+st28+"','"+st29+"','"+st30+"')" hSQL=SQLConnect(vsSQLDataSourceLocal); SQLBeginTran(hSQL); SQLSet(hSQL, "INSERT INTO [SterilizationVesselData].[dbo].[ProcessDataDetails] VALUES"); SQLAppend(hSQL,sqlAppStr1); SQLAppend(hSQL,sqlAppStr2); Status=SQLExec(hSQL, ""); MinuteDataReadPLC = 1; ! C830 Status=SQLExec(hSQL, ""); IF Status <> 0 THEN Message("Status Error During MM Data", SQLErrMsg(), 48); ErrLog("ERROR: SQL MINUTE MARKERS STATUS ERROR!"); SQLRollBack(hSQL); ELSE SQLCommit(hSQL); END SQLEnd(hSQL); SQLDisconnect(hSQL); END ! END of InsertMMSQLData
USE [SterilizationVesselData] GO /****** Object: Table [dbo].[ProcessDataDetails] Script Date: 3/13/2023 12:37:18 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ProcessDataDetails]( [DetailID] [int] IDENTITY(1,1) NOT NULL, [VesselID] [varchar](50) NULL, [LotNumber] [varchar](50) NOT NULL, [UserName] [varchar](50) NULL, [ManualOverride] [varchar](50) NULL, [DetailDate] [datetime] NOT NULL, [Rtd1A] [float] NULL, [Rtd1B] [float] NULL, [Rtd2A] [float] NULL, [Rtd2B] [float] NULL, [Rtd3A] [float] NULL, [Rtd3B] [float] NULL, [Pressure1] [float] NULL, [Pressure2] [float] NULL, [Fo1A] [float] NULL, [Fo1B] [float] NULL, [Fo2A] [float] NULL, [Fo2B] [float] NULL, [Fo3A] [float] NULL, [Fo3B] [float] NULL, [Heating] [float] NULL, [Cooling] [float] NULL, [WL] [float] NULL, [Steam] [float] NULL, [Cpp] [float] NULL, [Hxp] [float] NULL, [CppHxp] [float] NULL, [Flow] [float] NULL, [Statement] [nvarchar](50) NULL, [ProcessTime] [float] NULL, [SubLots] [varchar](50) NOT NULL, [Sublots1] [varchar](50) NOT NULL, [Sublots2] [varchar](50) NOT NULL, CONSTRAINT [PK_ProcessDataDetails] PRIMARY KEY CLUSTERED ( [DetailID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO