Date and MS SQL

Hi

Just started an evaluation, looks like a very useful product but have a couple of questions on dates in particular

I am attempting to load CSV data into a MS SQL table. Character and values are fine - however I get an error from the writer when I load a date into and DATE field - ie DATE not DATETIME, DateTime works fine. I have tried various Date Reformat configurations but can't find the answer - Is this possible. Also the same problem with a TIME field

The other problem is with the date format in my source file

For example

7/27/2014 12:00:00 AM is the time field - This is 12 midnight and I want to put this into SQL as 27/07/2014 00:00:00
Other times in the file continue 7/27/2014 1:00:00 AM - note the hours drop to one character


The date Reformat won't accept this single hour format (I think) and at best turns the 12:00 AM into 12:00:00

The PM values - ie 1:00:00 PM are not converted into 13:00:00 as you might expect

Is this possible with the date tools - I have resorted to a complex character parse to work around but very large and messy

Thanks

Richard

Richard

You can use custom date format inside date format transformation function

Peter

Thanks - replicated your Date format settings but as in the attached picture it does not produce the same result
This is from a Data File

I'll try the literal

Strange

Richard

You may have a situation when you have spaces at the end or at the beginning of 2 spaces between and that could be the source of the problem.

Perhaps you can zip and attach your file here.

Mike

Mike

File attached, simple tab delimited text file, can't see anything unusual myself. Would appreciate any guidance you can offer.
As mentioned, I can work around but its not pretty.

Note - the file "header" is awkward - had to ignore for field naming purposes - this is the text output from a web reporting tool.

If you also had an idea why the Writer refuses to place dates in an MSSQL table using a DATE field then that would also be interesting - but I'd guess both are related. DateTime works fine.

Using SQL Server 2008 R2 on a Windows 7 machine. Local Date setting English(Ireland)

Ideally a different input file could solve this but unfortunately the file format is fixed.

Thanks

Richard

"If you also had an idea why the Writer refuses to place dates in an MSSQL table using a DATE field then that would also be interesting - but I'd guess both are related. DateTime works fine."

Have you got 32 bit client for SQL Server 2008 installed?
That could be the reason.

Click Maitain - > ODBC manager

Here is what I have got



Mike