SQL Import data from Access - date issue

Question

I have table Customers in my access database which one I'm trying to import into a SQL Server table.

It keeps throwing an exception that it can't export the column DOB which is a MS Access date column.

So I did run the query against that table

select dob 
from customers   
where year(dob) < 1000

I got plenty of results. But when I check the MS Access table like

select dob 
from customers   
where isdate(dob) = false

Those records won't show up. So function ISDATE consider those dates as valid.

Is there any other workaround to grab all records from table Customers, which will actually allow me to insert those records into the SQL Server table?

Note: I need query not tool to do this.

I'm clueless. Why are dates with years above 1000 considered invalid? What do you really want to do? Sample data and desired results would help. — Gordon Linoff, Nov 17 '18 at 14:20
@GordonLinoff dates less than 1000 are considered invalid. I don't know how they get into my database but i need sql query which will ignore those dates . — PyDeveloper, Nov 17 '18 at 14:22

score 0 · Answer 1 · answered Nov 17 '18 at 14:40

0

It's pretty normal, since ISDATE function looks up for data type (not the value ) and returns boolean result.

On my perspective : What about editing dates lower than 1000 on SQL Server ?

First, feed all the datas to the sql db. Then, just do whatever you like with adding WHERE DATEFIELD<1000 clause to the end ?

answered Nov 17 '18 at 14:40

somethingunusual

84
6

Welcome to StackOverflow! Please provide a code or references to back up your answer. We have guidelines on [How to write a good answer](https://stackoverflow.com/help/how-to-answer). – Joseph Cho Nov 17 '18 at 17:22

score 0 · Answer 2 · answered Nov 17 '18 at 14:48

Actually i did a quick .exe with bulk insert from access to sql and i got detailed exception saying .

Bulk copy error : SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

So workaround was quite simple then as you can see

select dob  from customers    where dob between #1/1/1753# and
#12/31/9999#

Problems :

Ill miss the records with incorrect dates. Which is not to big issue at the end.

score 0 · Answer 3 · answered Nov 17 '18 at 14:55

0

Solution 1

Use a formula that adds 9000 for every date record lower than 1000

Feed all informations to db Do whatever you like on sql server

Solution 2

Update your exe code : Check your date field ( if its lower than 1000 or smth) in your code before insert action , if it is bigger than that feed it.

answered Nov 17 '18 at 14:55

somethingunusual

84
6

I mean, on solution 1 , check do whatever you want with your invalid records, by adding WHERE DATEFIELD>9000 clause to the end. – somethingunusual Nov 17 '18 at 14:57

SQL Import data from Access - date issue

3 Answers3