Troubleshooting WinCC V7.4 SP1 Date Time Errors in SQL Logging

Problem Description

When archiving tag values from WinCC Runtime V7.4 SP1 to Microsoft SQL Server 2014 via VBScript (or C script), the most common failure is a conversion error at the moment the INSERT statement touches a datetime, date, datetime2, or smalldatetime column. The WinCC runtime sends a date string that the SQL parser cannot interpret, and the batch is rejected with one of the following messages:

• Msg 241, Level 16, State 1, Line 1: Conversion failed when converting date and/or time from character string.
• Msg 242, Level 16, State 3, Line 1: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
• Msg 296, Level 16, State 3, Line 1: The conversion of char data type to smalldatetime data type resulted in an out-of-range value for smalldatetime value.

A typical installation logs an integer power reading (kW) once per hour from a power meter. The tag archive delivers the value fine, but the timestamp column throws the error above. The fault is reproducible and, more importantly, intermittent when the regional settings of the WinCC station, the SQL Server login, and the default language of the database user drift out of alignment.

Root Cause: Date Format vs. Date Order

SQL Server parses character-string → datetime conversions through two independent mechanisms:

1. The connection's language (set by SET LANGUAGE or by the login's default language).
2. The session's active DATEFORMAT setting (set by SET DATEFORMAT, which overrides the language default).

WinCC's built-in VBScript functions (Date, Time, Now, FormatDateTime) return strings in the regional short-date format of the runtime PC. On a German Windows installation that is dd.mm.yyyy; on a US installation M/d/yyyy; on a UK installation dd/mm/yyyy. SQL Server's default SET LANGUAGE US_ENGLISH expects mdy in most builds, so a value like 05/12/2019 is read as month 5, day 12 on a US host and day 5, month 12 on a German host. When the runtime sends 2019-12-05 thinking it is year-day-month, SQL Server interprets it as month 13, day 5 and raises Msg 242.

Because the failure depends on the combination of runtime locale, SQL login default language, and DATEFORMAT, a system that ran for years can break when one of the three is changed (for example, applying a Windows cumulative update, switching the SQL login, or installing a language pack on the engineering station).

Why the "SET LANGUAGE" Workaround Is Fragile

Setting the SQL Server login default language to match the WinCC station is a frequent recommendation, and it often appears to work because the same engineer touches the same machine for years. It fails under three documented conditions:

1. Connection pooling reuses a session with the previous DATEFORMAT. When WinCC opens the second connection in the pool, the residual SET DATEFORMAT dmy from a prior call is still active because the connection was not reset. Use sp_reset_connection in the connection string or set SET DATEFORMAT explicitly in every batch.
2. The login's default language is overridden by an explicit SET LANGUAGE in a stored procedure or trigger invoked by the INSERT path. This is common when the application calls master.dbo.xp_execresultset or runs inside a job step.
3. Database compatibility level is below 130. datetime2 and the FORMAT() function have stricter parsing rules than the legacy datetime type, so a string that worked on datetime in SQL 2014 can fail on datetime2 after a migration to SQL 2017+.

Solution 1 — Build the Date in VBScript (Recommended for Simple Logs)

For a single-process WinCC station logging one tag at a fixed cadence, build the timestamp from integer components. Integer components have no regional interpretation, so the string mm/dd/yyyy hh:nn:ss is delivered exactly as written, in the same order that us_english expects.

' WinCC V7.4 SP1 — Global Script (VBS) action
' Trigger: Tag "Trigger_Log" change, cyclic every 3600 s

Dim oConn, oRs, sSQL, sErr
Dim MyYear, MyMonth, MyDay, MyTime, Datum

MyYear  = Year(Date)
MyMonth = Month(Date)
MyDay   = Day(Date)
MyTime  = Time

' Always emit mm/dd/yyyy hh:nn:ss — unambiguous for us_english
Datum = Right("0" & MyMonth, 2) & "/" & _
        Right("0" & MyDay,   2) & "/" & _
        MyYear & " " & MyTime

Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = _
  "Provider=SQLOLEDB;" & _
  "Data Source=.\WINCC;" & _
  "Initial Catalog=EnergyArchive;" & _
  "User ID=wincc_logger;" & _
  "Password=********;" & _
  "AutoTranslate=False;" & _
  "Language=us_english;"

oConn.Open

sSQL = "INSERT INTO dbo.PowerLog (LogTime, kW) VALUES ('" & _
       Datum & "', " & CStr(CLng(SmartTag("KW_Actual"))) & ")"

oConn.Execute sSQL, , adExecuteNoRecords
oConn.Close
Set oConn = Nothing

Key points of the snippet:

• Right("0" & MyMonth, 2) zero-pads single-digit months to 04 instead of 4; SQL Server accepts both, but the padded form is required for datetime2 columns that are stored with millisecond precision in ODBC canonical (yyyy-mm-dd hh:mm:ss.fff) form.
• The connection string explicitly sets Language=us_english so the OLE DB provider issues SET LANGUAGE 'us_english' on every new physical connection.
• AutoTranslate=False prevents the OLE DB layer from converting character sets on a non-ANSI driver (CP1252 vs. CP850 on legacy installations).

Solution 2 — Use ISO 8601 Format and Set DATEFORMAT

SQL Server always recognises the ISO 8601 literals yyyy-mm-ddThh:mm:ss and yyyy-mm-dd hh:mm:ss regardless of language, provided the date order is preserved. This is the format Microsoft documents in Date and Time Data Types and Functions (Transact-SQL):

-- ISO 8601 is always parsed in ymd order by SQL Server
SET DATEFORMAT ymd;
INSERT INTO dbo.PowerLog (LogTime, kW)
VALUES ('2019-12-05T14:05:09', 412);

From VBScript this becomes:

Dim d, t, iso
d = Year(Date) & "-" & Right("0" & Month(Date), 2) & "-" & Right("0" & Day(Date), 2)
t = Right("0" & Hour(Time), 2) & ":" & Right("0" & Minute(Time), 2) & ":" & Right("0" & Second(Time), 2)
iso = d & "T" & t
sSQL = "SET DATEFORMAT ymd; INSERT INTO dbo.PowerLog (LogTime, kW) VALUES ('" & iso & "', " & _
       CStr(CLng(SmartTag("KW_Actual"))) & ")"

Combining the explicit SET DATEFORMAT ymd with the ISO literal makes the script independent of the SQL login's default language. The OLE DB Language= parameter in the connection string is then optional.

Solution 3 — Use the Server Clock (GETDATE() / SYSDATETIME())

If the SQL Server and the WinCC runtime PC are time-synchronised (recommended: same NTP source, max ±1 s skew), the most robust approach is to not send the timestamp from WinCC at all. Let the database stamp the row with GETDATE(), SYSDATETIME(), or SYSUTCDATETIME(). This removes every locale-related variable from the equation.

-- Table definition with a default
CREATE TABLE dbo.PowerLog (
    LogID   INT IDENTITY(1,1) PRIMARY KEY,
    LogTime DATETIME2(3) NOT NULL
        CONSTRAINT DF_PowerLog_LogTime DEFAULT (SYSDATETIME()),
    kW      INT          NOT NULL
);

' WinCC VBS — only the value is sent, the server fills the timestamp
oConn.Execute "INSERT INTO dbo.PowerLog (kW) VALUES (" & _
              CStr(CLng(SmartTag("KW_Actual"))) & ")", , adExecuteNoRecords

Function reference per Microsoft T-SQL date/time documentation:

Solution 4 — Convert Explicitly in the INSERT

When you must pass the timestamp as a string but cannot guarantee SET DATEFORMAT, wrap the value in CONVERT with an explicit style. The CONVERT style is parsed before the language lookup, so the string is interpreted purely by its pattern. Reference the complete style list in the T-SQL date and time documentation.

-- Style 120 = ODBC canonical 'yyyy-mm-dd hh:mm:ss'
INSERT INTO dbo.PowerLog (LogTime, kW)
VALUES (CONVERT(datetime2(3), '2019-12-05 14:05:09', 120), 412);

-- Style 101 = US 'mm/dd/yyyy'
INSERT INTO dbo.PowerLog (LogTime, kW)
VALUES (CONVERT(datetime2(3), '12/05/2019 14:05:09', 101), 412);

-- Style 104 = German 'dd.mm.yyyy'
INSERT INTO dbo.PowerLog (LogTime, kW)
VALUES (CONVERT(datetime2(3), '05.12.2019 14:05:09', 104), 412);

Solution 5 — Switch the Column to a Non-Ambiguous Type

Store the timestamp as datetime2 or datetimeoffset and let WinCC send the value as a numeric OLE Automation date (a Double holding days since 1899-12-30). This bypasses all string parsing and is the cleanest solution for high-rate logs.

' WinCC VBS — send as numeric OLE Automation date
Dim dValue
dValue = CDbl(DateAdd("d", 0, DateSerial(Year(Date), Month(Date), Day(Date)))) + _
         (Timer / 86400)

oConn.Execute "INSERT INTO dbo.PowerLog (LogTime, kW) " & _
              "VALUES (CAST(" & CStr(dValue) & " AS datetime2(3)), " & _
              CStr(CLng(SmartTag("KW_Actual"))) & ")"

On the SQL side, wrap the value with CAST(... AS datetime2(3)) so the OLE DB provider knows to use the numeric → datetime conversion path. datetime2 accepts the full range of Double values that VBScript can produce (1753-01-01 through 9999-12-31).

Connection String Reference for WinCC V7.4 SP1 → SQL Server 2014

Use the SQLOLEDB or SQLNCLI11 provider. The latter is required when connecting to SQL Server 2012+ with native client features.

Provider=SQLNCLI11;
  Data Source=<Server>\<Instance>;
  Initial Catalog=<Database>;
  User ID=<Login>;
  Password=<Password>;
  AutoTranslate=False;
  Language=us_english;
  DataTypeCompatibility=80;
  MARS Connection=True;
  Connect Timeout=15;

Verification Procedure

1. Open SQL Server Management Studio on the WinCC station and run SELECT GETDATE(). Compare the displayed format to what the WinCC VBScript action generates. They must match byte-for-byte (with the same date order and same time separator).
2. Execute a one-shot test from the WinCC VBScript debugger (Global Script → Debug) using the snippet from Solution 1 and a fixed value:
   oConn.Execute "INSERT INTO dbo.PowerLog (LogTime, kW) VALUES ('12/05/2019 14:05:09', 0)"
3. Inspect the inserted row with SELECT LogID, LogTime, kW FROM dbo.PowerLog ORDER BY LogID DESC. The LogTime column must read 2019-12-05 14:05:09.000, not 2019-05-12.
4. Trigger the cyclic logging action for ten cycles and check that SELECT COUNT(*) FROM dbo.PowerLog WHERE LogTime > DATEADD(MINUTE, -1, GETDATE()) returns ten rows.
5. Force a date-format stress test: change the SQL login's default language from us_english to British and verify that Solution 2 (ISO 8601 + SET DATEFORMAT ymd) still inserts the correct timestamp.

Troubleshooting Matrix

Best-Practice Recommendations for the Production Deployment

1. Use datetime2(3) for the timestamp column. The legacy datetime type rounds to .000, .003, or .007 seconds, which corrupts millisecond-resolution logs.
2. Use SYSDATETIME() on the server side whenever the runtime and the database are time-synchronised. This is the only solution that survives a WinCC station rebuild, a Windows locale change, and a SQL Server collation change simultaneously.
3. Set the SQL login's default language to us_english on principle, but do not rely on it. Always combine with an explicit format.
4. Use a stored procedure instead of inline INSERT. The procedure encapsulates the format logic and is auditable:

CREATE PROCEDURE dbo.usp_LogPower
    @LogTime DATETIME2(3) = NULL,
    @kW      INT
AS
BEGIN
    SET NOCOUNT ON;
    SET DATEFORMAT ymd;
    IF @LogTime IS NULL SET @LogTime = SYSDATETIME();
    INSERT INTO dbo.PowerLog (LogTime, kW) VALUES (@LogTime, @kW);
END

5. Log the exact error inside the VBScript with oConn.Errors(0).Description and route it to WinCC's alarm logging. The native error 241 is far more diagnostic in context than a silent gap in the database.
6. Disable pooled connection reuse for archive writes by appending ;OLE DB Services=-4 to the connection string when you cannot guarantee SET DATEFORMAT on every batch. This forces a fresh session for every write.
7. Document the WinCC runtime locale in the project header comment. Six months from now nobody will remember that the project started life on a German Windows installation.

FAQ