Import Dates into SQL

[tucker61]

I am migrating a Access database over to SQL, The dates in access were stored as Strings, whereas the SQL table they are moving into are the Datetime Format.

When i try the insert Function i get the error message below

What is the easiest way to change the format,

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

i have read about the Convert function but dont fully understand this.

 

[Ranman256]

in the append query, try inserting the field using delimiters:
# field #
or
dbl-quote " field "

I found both work, but the date layout must be mm/dd/yyyy
can you convert the access field to DATE/TIME type?

 

[arnelgp]

you should Alter the table in MSSQL and change the field to Date datatype.

 

[tucker61]

i have changed to DateTime datatype, and tried the above,

Here is my code
Should this work ?

insert into table1([EAN_No],[User_Name],[Budget_Code],[Location_Code],[Department_Code],[Department],[Category],[Trading_Depatment],[Reason_For_Return],[Return_Detail_1],[Return],[Return_Date],[Reason_For_Non_Return],[Non_Return_Detail_1],[Approval],[Update_Date],[Update_Time],[UserID],[Unique_ID])
Select [EAN_No],[User_Name],[Budget_Code],[Location_Code],[Department_Code],[Department],[Category],[Trading_Depatment],[Reason_For_Return],[Return_Detail_1],[Return],[Return_Date],[Reason_For_Non_Return],[Non_Return_Detail_1],[Approval],CONVERT(nvarchar(30),[Update_Date], 103),[Update_Time],[UserID],[Unique_ID]
From table2

 

[arnelgp]

you change it to Date, so that you don't need any conversion.

 

[tucker61]

Still getting conversion failed when changed to a Date field.

insert into Tble1([EAN_No],[User_Name],[Budget_Code],[Location_Code],[Department_Code],[Department],[Category],[Trading_Depatment],[Reason_For_Return],[Return_Detail_1],[Return],[Return_Date],[Reason_For_Non_Return],[Non_Return_Detail_1],[Approval],[Update_Date],[Update_Time],[UserID],[Unique_ID])
Select [EAN_No],[User_Name],[Budget_Code],[Location_Code],[Department_Code],[Department],[Category],[Trading_Depatment],[Reason_For_Return],[Return_Detail_1],[Return],[Return_Date],[Reason_For_Non_Return],[Non_Return_Detail_1],[Approval],[Update_Date],[Update_Time],[UserID],[Unique_ID]
From tble2

 

[Isaac]

How many columns in the source table are there? If not a lot, maybe you could write a loop through all the records and execute the insert by way of a pass through query (written in T-SQL) one at a time. Why I suggest that is you could take advantage of t-sql's TRY_CAST and TRY_CONVERT options, which are precisely what you need right now.

 

[tucker61]

How many columns in the source table are there? If not a lot, maybe you could write a loop through all the records and execute the insert by way of a pass through query (written in T-SQL) one at a time. Why I suggest that is you could take advantage of t-sql's TRY_CAST and TRY_CONVERT options, which are precisely what you need right now.

19 columns in total

 

[Isaac]

Now that I think about it more, I believe my suggestion is fatally flawed, because: you would lose the benefit of try_cast because by the time you are processing row by row, you might as well test if Access's date is correct anyway.

So first I wouldn't suggest you change your SQL DateTime to Date, unless that's actually what you want! (no time component).

Are your Access strings containing a time component or not?

My general approach in sql server is to use DateTime. You can always strip out the time component in queries; it's less easy to go back in time and capture a timestamp.

So the things you need to consider are two-fold.

1) in Access, converting the string to a date as you do the insert.
2) in Access, doing a date range comparison, to be sure SQL will accept it

I would research it from those two aspects - both will need to be covered.

 

[Pat Hartman]

If this is a one time conversion, add a proper date data type field on the record. Run an update query to format the string as a date and update the new column. Run a query at the end to return any rows where the string is not null and not = ZLS and the newdate field is null

I tried to include the code but I'm getting an error. I'll report it to Jon. Sorry.

 

[Pat Hartman]

I'm posting this as an image because I got an error when I posted the code directly.

 

[tucker61]

Sorted thus in the end, we used the cknvert function and had to break the string down to its backs co.ponenrs, yyyy mm dd.

Also when looking at some of the records they were not all saved as dates, so this gave me some issues.

Thaks for all the help.