Resolving S7-1200 DTL Date Format Errors in WinCC RT SQL Writes

Problem Description

On a SIMATIC PC station running WinCC Runtime (RT) or WinCC Professional Runtime, a VB Script reads a DTL (Date and Time Long) tag from a SIMATIC S7-1200 controller over the integrated SIMATIC S7-1200/S7-1500 channel (or via OPC) and pushes the value into a Microsoft SQL Server table. The S7-1200 firmware exposes every component of the DTL structure individually — YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, NANOSECOND — and the VB Script concatenates them into the regional format DD/MM/YYYY hh:mm:ss. The value is written as a string, but the destination column is a datetime or datetime2 column. SQL Server silently converts the string using the session's DATEFORMAT setting — which on US-English and most default installations is mdy. As a result:

• Days 01 to 12 are accepted (SQL re-orders them as MM/DD/YYYY, producing the wrong day/month but still a valid date).
• Days 13 through 31 trigger Msg 242, Level 16, State 3 — The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
• Insert failures pollute the upstream VB error log and leave the SQL row absent; production historians get gaps.

Root Cause Analysis

Three independent failure paths combine in this scenario:

1. Regional date literal convention. The DTL field on the S7-1200 is a 16-byte IEC 61131-3 structure (see SIMATIC S7-1200 Programmable Controller System Manual, section "Date and time data types"). Its DAY, MONTH, and YEAR members are pure unsigned integers and carry no format hint. Any consumer is responsible for assembling a locale-correct string.
2. SQL Server language-dependent parsing. Per Date and Time Data Types and Functions (Transact-SQL), the session language and DATEFORMAT setting govern how character literals without ODBC escape sequences are converted. The default US-English session sets DATEFORMAT mdy; the German/European session sets DATEFORMAT dmy. Same script, two SQL servers, two opposite behaviors.
3. Hidden cast in INSERT. A bare INSERT INTO tbl (TimeStamp) VALUES ('25/03/2024 14:33:10') triggers an implicit varchar → datetime conversion. The implicit conversion is the only place the regional bias is applied, so it is the only place to fix.

Environment and Toolchain

Solution 1 — SET DATEFORMAT dmy at the Session Level

The fastest zero-schema-change fix is to prepend the batch with SET DATEFORMAT dmy; so every literal in the same connection is parsed in day-month-year order. This works from VB Script by issuing two Execute calls on the same open ADODB.Connection.

VB Script (WinCC RT)

Dim conn, cmd, rs
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=MSOLEDBSQL;Server=.\SQLEXPRESS;" & _
                        "Database=Production;Trusted_Connection=Yes;"
conn.Open

' Force European date parsing for THIS connection
conn.Execute "SET DATEFORMAT dmy;"

Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn

' Read DTL fields directly from the S7-1200 tag
Dim dDay, dMon, dYear, dHour, dMin, dSec
dDay  = SmartTags("DB_MyTime.DAY")
dMon  = SmartTags("DB_MyTime.MONTH")
dYear = SmartTags("DB_MyTime.YEAR")
dHour = SmartTags("DB_MyTime.HOUR")
dMin  = SmartTags("DB_MyTime.MINUTE")
dSec  = SmartTags("DB_MyTime.SECOND")

cmd.CommandText = "INSERT INTO dbo.ProcessLog (TagName, EventTime, Value) " & _
                 "VALUES ('ReactorTemp', '" & _
                 Right("0" & dDay, 2) & "/" & Right("0" & dMon, 2) & "/" & dYear & " " & _
                 Right("0" & dHour,2) & ":" & Right("0" & dMin,2) & ":" & Right("0" & dSec,2) & _
                 "', 84.2)"
cmd.Execute

conn.Close
Set cmd = Nothing
Set conn = Nothing

Solution 2 — ISO 8601 Unseparated Format (Recommended)

Microsoft's T-SQL documentation explicitly states that 'YYYYMMDD' literals are interpreted using ISO 8601 rules regardless of SET DATEFORMAT or session language — they are always interpreted as year-month-day-hms. This is the only format that survives a SQL Server migration to a different collation language.

VB Script producing ISO 8601

Function DTL_To_ISO8601(ByVal y, ByVal m, ByVal d, _
                        ByVal hh, ByVal mm, ByVal ss)
    DTL_To_ISO8601 = Right("0000" & y, 4) & Right("00" & m, 2) & _
                     Right("00" & d, 2) & "T" & _
                     Right("00" & hh,2) & ":" & Right("00" & mm,2) & ":" & _
                     Right("00" & ss,2)
End Function

Dim sStamp
sStamp = DTL_To_ISO8601(SmartTags("DB_MyTime.YEAR"), _
                        SmartTags("DB_MyTime.MONTH"), _
                        SmartTags("DB_MyTime.DAY"),   _
                        SmartTags("DB_MyTime.HOUR"),  _
                        SmartTags("DB_MyTime.MINUTE"),_
                        SmartTags("DB_MyTime.SECOND"))
' sStamp = "20240325T14:33:10"

cmd.CommandText = "INSERT INTO dbo.ProcessLog (TagName, EventTime, Value) " & _
                 "VALUES ('ReactorTemp', '" & sStamp & "', 84.2)"

For sub-second precision, append the DTL NANOSECOND field as milliseconds (drop the last six digits, keep the first three) — for example "20240325T14:33:10.457". Pair the column type with datetime2(3) or higher to avoid 8 ms rounding artifacts introduced by the older datetime type.

Solution 3 — Parameterized Command with Typed ADODB.Parameter

Eliminating the implicit string-to-date conversion entirely is the most robust approach. Build a parameterized INSERT, then populate the parameter using Parameter.Value = CDate(...). Because the value is delivered as a Variant/Date, ADO sends it as the native datetime type — no string parsing happens on the server.

Dim conn, cmd, prm
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=MSOLEDBSQL;Data Source=.\SQLEXPRESS;" & _
          "Initial Catalog=Production;Integrated Security=SSPI;"

Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO dbo.ProcessLog (TagName, EventTime, Value) " & _
                 "VALUES (?, ?, ?)"
cmd.Parameters.Append cmd.CreateParameter("Tag",     200, 1, 50, "ReactorTemp")

' Build a VBScript Date value from the DTL components
Dim d : d = DateSerial(SmartTags("DB_MyTime.YEAR"), _
                       SmartTags("DB_MyTime.MONTH"), _
                       SmartTags("DB_MyTime.DAY"))
Dim t : t = TimeSerial(SmartTags("DB_MyTime.HOUR"), _
                       SmartTags("DB_MyTime.MINUTE"), _
                       SmartTags("DB_MyTime.SECOND"))
cmd.Parameters.Append cmd.CreateParameter("EventTime", 7, 1, 0, CDate(d & " " & t)) ' adDate

cmd.Parameters.Append cmd.CreateParameter("Value", 5, 1, 0, CDbl(84.2))              ' adDouble
cmd.Execute

Solution 4 — Connection-String Date Language Override

For deployments that touch multiple databases or where setting SET DATEFORMAT per insert is impractical, declare the language at the connection level so SQL Server applies the matching DATEFORMAT when it parses literals:

Provider=MSOLEDBSQL;Server=.\SQLEXPRESS;Database=Production;
        Integrated Security=SSPI;Date Language=British;

The corresponding SET LANGUAGE British forces DATEFORMAT dmy. This is one of the few connection-string parameters that survives SET DATEFORMAT statements later in the session because SET LANGUAGE resets the date format to the language default.

Verification Procedure

1. Open SQL Server Management Studio, connect to the same ODBC data source the WinCC RT uses, and execute the generated INSERT manually — confirm a row is added and the EventTime matches the S7-1200 clock within 1 s.
2. In WinCC RT, add a status text tag (SQL_LastError) and write the cmd.Execute error description into it using On Error Resume Next / Err.Description capture.
3. Run a regression test with deliberate boundary dates: 2024-01-01 00:00:00, 2024-02-29 08:30:00 (leap day), 2024-12-31 23:59:59, and 2025-01-13 12:00:00 (the historically failing day-of-month > 12).
4. Query the destination table:
   SELECT TagName, EventTime FROM dbo.ProcessLog ORDER BY EventTime DESC; — verify continuous coverage across the 12/13-day boundary.
5. Stop the WinCC RT service, restart it, and re-run the script — guarantees that pooled connections are not holding a stale mdy session.

Troubleshooting Matrix

Production-Hardening Checklist

• Always log to a secondary SQL Server table before the main insert (use an Output clause or a separate INSERT INTO dbo.ProcessLog_Audit) so DTL & string intermediate values can be reconstructed when a row is rejected.
• Wrap the batch in BEGIN TRY … END TRY BEGIN CATCH … END CATCH via a stored procedure; bubble ERROR_NUMBER(), ERROR_MESSAGE(), and ERROR_SEVERITY() back to the WinCC status tag.
• Use Windows Authentication (Integrated Security=SSPI) — avoids stored passwords in the WinCC project that ship in the runtime database.
• Set the WinCC Runtime service to start with the local SYSTEM account or a domain service account that owns db_datawriter on the target database; never interactive desktop accounts.
• For high-frequency logging, batch in 10–50-row transactions using table-valued parameters instead of single-row inserts to avoid log flush overhead.
• Lock the time-zone semantics: store UTC in the table, convert to local time only on read; this makes the historian immune to DST or plant-time-zone changes.
• Open connection once per script activation and reuse it; ADO pooling keeps the SET DATEFORMAT alive across inserts.

Edge Cases and Field Notes

NANOSECOND precision. S7-1200 DTL carries nanoseconds (10⁻⁹ s). SQL Server's datetime is rounded to .000, .003, .007; datetime2 rounds to 100 ns. Stripping NANOSECOND entirely is acceptable for events slower than 1 s; downsampling to milliseconds and aligning to datetime2(3) is the recommended compromise for high-speed alarms.

DTL validity. The DTL structure has a VALID flag in STATUS. After a CPU warm restart or stop/run transition, the value can return to DTL#1970-01-01-00:00:00 until the next WR_SYS_T / SET_TIMEZONE write in TIA Portal. Always check SmartTags("DB_MyTime.STATUS") bit 7 before building the SQL literal.

WinCC VBScript locale. The WinCC RT script engine uses the regional settings of the Windows user account that started the runtime service. Combine this locale with the SQL session SET LANGUAGE to either pin both sides to one convention or use ISO 8601 on both.

WinCC Professional vs TIA Portal WinCC. The above advice applies to both WinCC Comfort/Advanced panels and WinCC Professional Runtime PC stations — both expose the SmartTags() object for tag access and a VBScript editor. TIA Portal V18 also adds the option of using C# scripts with SqlClient.SqlCommand, which sidesteps the ODBC string literal problem entirely because of typed parameters.

Reference: Quick Decision Tree

If the database can be touched by every VB script writer, prefer Solution 3 (parameterized). If the script must remain pure-string without schema changes, prefer Solution 2 (ISO 8601). If the database is shared with other legacy tools that overwrite the connection setting, use Solution 1 locally at every conn.Open and back it up with Solution 4's connection-string language hint. Always pair with a production SQL stored procedure and try/catch for real-world reliability.

FAQ