Custom field conversion for import? 2

[gbaughma]

I have a flat-file that came from DB2... DB2 has an unrecognized date format.

I was wondering if it is possible to write a custom conversion for use in the Import/Export wizard?

The datetime is in YYYY-MM-DD-HH.MM.SS.ssssss and SQL doesn't recognize it.

How would I go about "adding" the ability to recognize that format, and convert/import it to a SQL DateTime field?

Thanks in advance!


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[gbaughma]

Oh.. sorry... SQL Server 2008 R2....


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[gmmastros]

Declare @Original VarChar(30)

Set @Original = '2014-12-12-15.43.14.123'

Select	Convert(DateTime, Replace(Stuff(Replace(@Original, '.', ':'), 11, 1, ' '), '-', ''))

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

Yeah, that's what I was doing... I was bringing in the flat file into a temporary table, then doing an INSERT INTO statement, doing transforms along the way. A whole bunch of casts and converts and lefts and rights....

I was hoping to actually extend the capability of the Import wizard, so that it could handle that particular datetime format. I don't know if there is something I can do in the SSIS10TOoMSSQL.XML file, or in the DtwTypeConversion.XML file to make this work. I'm poking around, but I'll bet what I'm looking for (as far as parsing strings and spinning them around and turning them into a date or whatever) is in a DLL.

I have HUGE files to import... one of them doing it your way took me all day. If I can get the Import wizard to recognize a "custom" field type with the proper conversion, it would be a relative walk in the park.


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[fredericofonseca]

you can do it on ssis - extract data as a string datatype and then use a derived column to convert to datetime2 using substrings/replaces, or even a c# component.

note that wizard will not allow you to do it and you need to do the SSIS package manually (or if you are brave enough save the wizard package and change it)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gbaughma]

So this is something I have to do in VisualStudio?


Just my 2ó

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

 

[fredericofonseca]

yes but what you need is part of your sql server install - no need for anything extra

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886