Excel tip – How to convert .NET DateTime to SQL DateTime

I needed to do some data transformation today and part of that was to convert datetime from form:

5/31/2011 11:22:18 AM (.NET US-culture DateTime)

Into

2011-05-31 11:22:18.000 (SQL Server DateTime format)

I wanted to do this quick and for some reason Excel was unable to convert the datetime using its functions. So, I needed to prepare following transformation function:

=CONCATENATE(MID(C90874;FIND("/";C90874;FIND("/";C90874;1)+1)+1;FIND(" ";C90874;
FIND("/";C90874;FIND("/";C90874;1)+1)+1)-FIND("/";C90874;FIND("/";C90874;1)+1)+1-2);"-";
CONCATENATE(IF(LEN(MID(C90874;1;FIND("/";C90874;1)-1))=1;"0";"");MID(C90874;1;
FIND("/";C90874;1)-1));"-";CONCATENATE(IF(LEN(MID(C90874;FIND("/";
C90874;1)+1;FIND("/";C90874;FIND("/";C90874;1)+1)-FIND("/";C90874;1)-1))=1;"0";"");
MID(C90874;FIND("/";C90874;1)+1;FIND("/";C90874;FIND("/";C90874;1)+1)-
FIND("/";C90874;1)-1));" ";IF(MID(C90874;FIND(" ";C90874;FIND(" ";C90874;1)+1)+1;
LEN(C90874)-FIND(" ";C90874;FIND(" ";C90874;1)+1))="PM";VALUE(MID(C90874;
FIND(" ";C90874;1)+1;FIND(":";C90874;1)-FIND(" ";C90874;1)-1))+12;
IF(LEN(MID(C90874;FIND(" ";C90874;1)+1;FIND(":";C90874;1)-FIND(" ";C90874;1)-1))=1;
CONCATENATE("0";MID(C90874;FIND(" ";C90874;1)+1;FIND(":";C90874;1)-
FIND(" ";C90874;1)-1));MID(C90874;FIND(" ";C90874;1)+1;FIND(":";C90874;1)-
FIND(" ";C90874;1)-1)));":";IF(LEN(MID(C90874;FIND(":";C90874;1)+1;
FIND(":";C90874;FIND(":";C90874;1)+1)-FIND(":";C90874;1)-1))=1;CONCATENATE("0";
MID(C90874;FIND(":";C90874;1)+1;FIND(":";C90874;FIND(":";C90874;1)+1)-
FIND(":";C90874;1)-1));MID(C90874;FIND(":";C90874;1)+1;FIND(":";C90874;
FIND(":";C90874;1)+1)-FIND(":";C90874;1)-1));":";IF(LEN(MID(C90874;
FIND(":";C90874;FIND(":";C90874;1)+1)+1;FIND(" ";C90874;FIND(" ";C90874;1)+1)-
FIND(":";C90874;FIND(":";C90874;1)+1)-1))=1;CONCATENATE("0";MID(C90874;FIND(":";
C90874;FIND(":";C90874;1)+1)+1;FIND(" ";C90874;FIND(" ";C90874;1)+1)-FIND(":";
C90874;FIND(":";C90874;1)+1)-1));MID(C90874;FIND(":";C90874;FIND(":";
C90874;1)+1)+1;FIND(" ";C90874;FIND(" ";C90874;1)+1)-FIND(":";C90874;
FIND(":";C90874;1)+1)-1));".000")

Summary of function features:

  • C90874 is the source Cell, just replace with your cell
  • It automatically converts AM/PM into 24hour format
  • It should do left Zero padding into two number format

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s