SQL native pattern datetime issue

1

Using NServiceBus : 5.2.14 , with SQL transport.

Trying to insert message using SQL native, for message header assigning TimeSent value, Tried below two options

  1. “NServiceBus.TimeSent” : “’ +convert(varchar, GETUTCDATE(), 121)+ '”
  2. “NServiceBus.TimeSent” : “‘+convert(varchar, GETUTCDATE(), 121)+’ Z”

as suggested at Message Headers • NServiceBus • Particular Docs

Message processed succussfully but getting below error on log & in ServiceInsight

NServiceBus.ExceptionInfo.StackTrace	System.FormatException: String was not recognized as a valid DateTime.
   at System.DateTimeParse.ParseExact(String s, String format, DateTimeFormatInfo dtfi, DateTimeStyles style)
   at NServiceBus.DateTimeExtensions.ToUtcDateTime(String wireFormattedString) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\DateTimeExtensions.cs:line 26
   at NServiceBus.ProcessingStatisticsBehavior.Invoke(IncomingContext context, Action next) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Monitoring\ProcessingStatisticsBehavior.cs:line 12
   at NServiceBus.BehaviorChain`1.InvokeNext(T context) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 107
   at NServiceBus.BehaviorChain`1.<>c__DisplayClass4_0.<InvokeNext>b__0() in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 95
   at NServiceBus.Metrics.ServiceControl.ServiceControlMonitoring.ServiceControlMonitoringRegistrationBehavior.Invoke(IncomingContext context, Action next)
   at NServiceBus.BehaviorChain`1.InvokeNext(T context) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 107
   at NServiceBus.BehaviorChain`1.<>c__DisplayClass4_0.<InvokeNext>b__0() in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 95
   at NServiceBus.AuditBehavior.Invoke(IncomingContext context, Action next) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Audit\AuditBehavior.cs:line 20
   at NServiceBus.BehaviorChain`1.InvokeNext(T context) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 107
   at NServiceBus.BehaviorChain`1.Invoke() in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\BehaviorChain.cs:line 52
   at NServiceBus.Pipeline.PipelineExecutor.Execute[T](BehaviorChain`1 pipelineAction, T context) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Pipeline\PipelineExecutor.cs:line 129
   at NServiceBus.Unicast.Transport.TransportReceiver.OnTransportMessageReceived(TransportMessage msg) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Unicast\Transport\TransportReceiver.cs:line 413
   at NServiceBus.Unicast.Transport.TransportReceiver.ProcessMessage(TransportMessage message) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Unicast\Transport\TransportReceiver.cs:line 344
   at NServiceBus.Unicast.Transport.TransportReceiver.TryProcess(TransportMessage message) in C:\BuildAgent\work\3206e2123f54fce4\src\NServiceBus.Core\Unicast\Transport\TransportReceiver.cs:line 230
   at NServiceBus.Transports.SQLServer.AmbientTransactionReceiveStrategy.TryReceiveFrom(TableBasedQueue queue) in C:\BuildAgent\work\888dd942b44bad5a\src\NServiceBus.SqlServer\AmbientTransactionReceiveStrategy.cs:line 57

NserviceBus%20Datetime
NserviceBus%20Datetime1911×975 498 KB

2

Could resolve this by using

“NServiceBus.TimeSent” : “’+format(GETUTCDATE(),‘yyyy-MM-dd HH:mm:ss:ffffff Z’) +’”

3

The timestamp format should be yyyy-MM-dd HH:mm:ss:ffffff Z

According to the SQL docs 121 will not result in the same format but in yyyy-mm-dd hh:mi:ss.mmm

The difference is the semicolon versus the dot for the milliseconds.

4

Thanks for response, as i have mentioned below format working for me.

“NServiceBus.TimeSent” : “’+format(GETUTCDATE(),‘yyyy-MM-dd HH:mm:ss:ffffff Z’) +’”

Thanks.