Importing dates from Access 2000 to SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Importing dates from Access 2000 to SQL 2000

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-23 : 05:21:34

I'm trying to import dates and times from access2k to sql2000.

The date in access is [fulldate] and is TEXT and format dd/mm/yyyy consistently throughout the entire database. But note that it's not in access DATE FORMAT.

The time is [INDEXTIME] and is also in text format in 00:00:00 PM/AM format. Also consistent throughout the entire table.

The field in SQL 2000 is DATETIME.
I'm importing from an ASP page. When I construct the stored procedure in SQL, it looks like this:

-----------------------------------------
Create Procedure...
(@indexid as int, @indexdate as datetime)
as
update indexdata set indexdate = @indexdate where indexid = @indexid.
go
-----------------------------------------
In asp I do the following:
Dim TheDateAndTime
TheDateAndTime = rs.fields.item("fulldate").value & " " & rs.fields.item("indextime").value

When I do a response.write(TheDateAndTime) I get:
14/01/2000 01:01:33 PM -> which is obviously perfectly right.
When I pass this through my command object in ASP to assign to @indexdate in Stored Procedure, I get numerous errors. From "unable to convert varchar to datetime" to "unable to convert string to datetime" or "unable to convert datetime to integer".

Why is it so flippen difficult to insert a valid date into SQL2K?

I have also tried converting the text fields [fulldate] and [indextime] to Date in Access, even though it converts successfully, it still won't insert through the stored procedure and gives exactly the same errors.

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-23 : 05:35:55

I managed to get it to work - Maybe this can help someone else:

First set the correct LCID in ASP Page at the top:
<%Session.LCID = 2057 %>
Now we know we're working in dd/mm/yyyy UK format.

Then,
instead of saying IndexDate = rs.fields.item("fulldate").value & " " & rs.fields.item("indextime").value

You should say:
IndexDate = year(rs.fields.item("fulldate").value) & "/" & month(rs.fields.item("fulldate").value) & "/" & day(rs.fields.item("fulldate").value) & " " & rs.fields.item("indextime").value

That way you get it in SQL ISO standard and it will insert without a problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources