August 4, 2006 at 7:36 am
Hi Guys,
I have a DTS Package that imports data from Excel files and inserts them into a sqlserver database. One of the columns that is inserted is a date field. Lately the following has started to happen.
Occasionly (its seems when it wants to) the DTS package imports the date values as american format and other times in the american format... What I need is for the data to be inserted in the UK format i.e. dd/mm/yy
The default language of the server is United states american english and the default language of the user under which the job is running is american. I have also in the property field of the package transformation(i.e. on the column source - destination) choosen to import the data in the following format
source dd/MM/yy
destination dd/MM/yy
Now the excel files are coming into an exchange mailbox in the format dd/mm/yyyy so I cannot see how this could be possibly going wrong.
One of the things I have tried is changing the default language of the user that is running the job but that just converts the data to american format everytime.
another thing to note is that after the 12th of everymonth, data inserted reverts back to the english format the way I want it. but its just all over the place for the days coming up to that...
If anyone can help thanks a million cos I have been at this for weeks now... without success... on my own... banging my head... against the desk... punching the screen... while shouting obscenities... all while crying...
Thanks again,
M
August 7, 2006 at 8:00 am
This was removed by the editor as SPAM
August 8, 2006 at 2:47 am
The dates you are importing are in the UK format. So, in order for SQL Server to interpret them as UK-format dates, I would have thought that the user who does the import needs to have British English as its default langauage. If that doesn't work, try changing the dates in the spreasheet to an unambiguous format, such as yyyy-mm-dd.
Let us know how you get on
John
August 8, 2006 at 3:00 am
Hi John,
Cheers for the reply. See the thing is that the dates are sent in from local offices to an exchange mailbox. From this my task retrieves the file and imports the data, thus I've no real way of changing the data format unless I go down to each local office and change their settings. Which I need not add is unfeasible...
I just wish the logical answer was the right one.
Any more feedback and I'd be really grateful. Thanks again,
Martin
August 8, 2006 at 3:34 am
Martin
Did you try changing the default language of the login? Having reread your original post more carefully, I think that this is the only thing it could be. The data is coming in in the correct format, yet is being wrongly interpreted when it goes into the database. Once in the database it is stored as a number - SQL Server doesn't really care about the format: that's just a presentational issue. Therefore I would bet that your login is looking at 07/08/06 and interpreting it as 8th July 2006 instead of 7th August.
John
August 8, 2006 at 3:57 am
Hi John,
Thanks again for the reply. Thats one of the things I tried at the very begining. Initially it was set at being just english(US English). So then when this problem start occuring I changed it to Bristish English in the hope that it would fix it. Bizarely though, this made it even worse inserting every record into the database in the american format, MM/DD/YYYY. Which was baffling... If you have any more Ideas once again, thanks a million. See the thing is that there is no pattern to this problem. The job runs every hour and sometimes can be fine, and others not. Even records within the same file can end up being rendered incorrectly... This really is baffling. Do you think it could be something to do with the OS settings, or even Excel settings?
Kind Regards,
Martin
August 8, 2006 at 4:14 am
Martin
That's strange. I think you're right - the next thing you need to look at is the regional settings of the computer on which you're running the import. I think Excel picks up its settings from there.
Just as an aside, all this talk of formatting troubles me. Either you have the right dates in your database or you have the wrong dates. So please will you answer what probably appears to be a stupid question: how do you know that the dates are wrong?
Another thing to consider: are you sure that the dates coming in from the remote offices are correct? For example, there may be a date in the spreadsheet that says 04/08/06, but are you sure that this wasn't entered by the end user as 08-Apr-2006?
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply