Date Conversion

[HLPCH]

I used the DTS Import wizard to import data from Microsoft Accesss. In one of the tables , I have column(lastdate) which is of Date type in Access. When i import this to SQL server it gives me an Overflow Error. I am importing this field as a DateType field in SQL.

It works when I change the Datatype to nVarchar. But i do not want this as text or Varchar.

Could some one tell how I could eliminate this problem?

Thanks

 

[jbenson001]

Can you post some sample data you are trying to import?

 

[HLPCH]

Here are some of the samples:

Lastdate:10/2/2004
1/24/2000
10/5/2000

Some are null as well. Thanks

 

[jbenson001]

The date format looks OK, however the NULLs may cause a problem unless you allow NULLs in your column definition.

 

[SQLBill]

You need to tell SQL Server how to interpret those values.

What is 10/2/2004?
October 2, 2004
or
10 February 2004

What is 1/24/2000?
January 24, 2000
or the first day in some unknown month of 2000?

I don't DTS, so I'm not sure how to do it, but in normal SQL Server script I would begin with:
SET DATEFORMAT MDY

-SQLBill

Posting advice: FAQ481-4875

 

[DrSql]

Goto a DB==>Table==>Right click and import data, Source==> Access file==>Destination==>SQLServer Db==>import the table and see you get any error. Let the wizard create a table by itself. See the date field have any upnormal vlaues and see type of the filed. This way you can narrow down whats the issue.

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[SQLBill]

If possible, change the DATE format in your Access tables to be YYYYMMDD.

SQL Server understands that without being told how to interpret it.

-SQLBill

Posting advice: FAQ481-4875