IDT datetime2(7) and date parse error

[Former Member]

I'm getting a parse error with datetime2(7) and date data types. The data foundation is displaying the columns as 'AB' (varchar).

I'm using SQL Server 2008. Anyone else experiences this / have a solution?

This is a real issue for me as Data Services creates all columns as datatime2(7) by default.

[Former Member · Accepted solution]

I have resolved this by:

• Repairing SQL Server Native Client via Add/Remove Programs (local machine)
• Recreating the ODBC entry used by the Connection in System DNS (local machine)
• Refreshing the structure in the Data Foundation in IDT

[Former Member]

Use datetime instead of datetime2 unless you particularly need ten thosandths of a second.

Seems a totally useless data format for normal life from what I've looked at. Obviously there will be exceptions like working with phantom cameras (super slow motion cameras) but for standard transactions, three decimal places of a second seems perfectly adequate.

[Former Member]

I totally agree Mark. My problem is that when selecting datetime as a datatype in Data Services and creating the table via Data Services, the datatype for the column that gets created in the database is datetime2.

I think my only option at present is to update all the date columns manually after Data Services creates them and reimport them.

Once the tables are established in the DW there will be no problem but right now I'm building the DW from scratch.

[nscheaffer]

Could you remove the date column that is being converted incorrectly to varchar and then insert a calculated column to force the conversion to the data type you want?

Does that help?

Noel

[Former Member]

Thanks for you response. Although your suggestion is an option I'm looking for something that is not a workaround.