Date problem transferring Access table to SQl (1 Viewer)

[Dennisk]

Hello all,

I don't use SQL that much and I'm bewildered that MS SQL server cannot import and Access table with english Dates of Birth ie 22/12/1970. It fails to convert with an error message, then if I change the destination date field to a character field it will then import, but the Dates are now in american format ie 12/22/1970. What would be the best technique to use.

 

[Pauldohert]

This may or may not be helpful - but I always pass dates to datetime arguments in sql as

@Start = '" & Format(cbFrom, "mmmm dd yyyy") & "'

etc

This way the UK/US format never seems to be an issue??

Perhaps something similar would help in your import? I am asuming your destination table does accept UK dates?

 

[Dennisk]

Paul,
Thanks for the reply however I am using the DTS service to Import. I can amend the transformation and convert the date but I do not know what functions are available in the VB script that the DTS package uses as this is not in BOL.

 

[Dennisk]

Well I've sorted this problem out. I had to create a DTS package ans set up an error log and its dates such as 01/12/1003 that are causing the problem. If you didn't know how to set up a package and just relied on the wizard you would not get anywhere with this.