transferring datetime data between databases

i'm trying to transfer existing tables to a new database. the tables i'm transferring from/to don't have the same structure/field names (trying to improve what i already have), so what i'm doing is opening a connection to both databases, getting a recordset from one, creating a sql string to execute, and executing that on the new database for each record. my problem is with the datetime field. in both tables it's labelled as "DBTimeStamp" (i created it as datetime), but when i try to copy it into the new database, i get the error "The name 'Fri' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted." of course, when i put single quotes like it's a string, i get the "date cannot be converted from string" error... how can this be done? what do i need to do to the existing datetime to make it suitable for inserting into the new database??

Are you trying to copy a string (date) into a datetime field? If so, make sure that the string value you are trying to copy over is acceptable in a datetime field. Also, SQL Server has an easy to use export feature. Right click on the database, select Export Data and continue with the wizard. This way, you don't have to do all of this programmatically. Hope this helps.

is there any way to NOT retrieve the date as a string? i tried both of these:

user_regDate = RS("user_regDate").value;

user_regDate = RS("user_regDate");

and for both, the insert complains that it's a string...

might it be a better idea to transfer directly from one recordset to another, rather than assembling an insert string? ie:

RS1 is the recordset retrieved from the old db
RS2 is an empty recordset from the new db

RS2.Open(/*connect to users table*/);
RS2.AddNew();
RS2("user_regDate") = RS1("user_regDate");
RS2.Update();

would that work?? (i'd try it right now but i have to get going to work!)

thanks, any help is appreciated.

Well, I suggest that u try SQL Server's export features so that you don't have to go through the trouble of doing this programmatically. Second, Send me a sample date string that you are trying to insert into the second recordset. (such as '10/10/2003 10:00:00AM")

Thanks

here is the type of string i get when i try to run the code:

Fri Mar 21 23:15:38 PST 2003

(... and actually, that's supposed to be eastern time... i added 3 hours so that it would be stored as eastern time but i obviously overlooked that it still labels it as PST. a side question would be how can i fix that? my db is hosted in california, i'm in new york...)

how do i use the export function? in changing to new tables, i want some new field names and some additional fields, am i able to control that sort of thing in using the export function? or does it simply duplicate an entire table?

thanks

The problem is that a datetime field will not accept that value. It accepts values like '10/10/2004 3:04 pm'. If you really want to store that string as a datetime field, you are going to have to parse it into a valid datetime value.

sweet, i got it. i had to take the string, split it, change the month to a number, add a leading zero to the day if necessary, and assemble the string as YYYYMMDD HH:MM:SS

thanks for your help!

i'm going to look into the export/import thing more, too.