Date formatting problems

Hi,

I have around 1000 records each with two dates in a database in MSDE onmy PC. I need to move the data to an on line SQL server and havetried to use Microsoft Web Data Administrator to do this. I canexport the data from the MSDE to an SQL file but it will not importbecause the date format in the SQL file is "dd,mm,yyyy".

If I export from either the MSDE or the SQL server both produce fileswith date in the format "dd,mm,yy" and yet I cannot import either!?!

It is impractical to change all the dates by hand. I am on abudget and do not have access to anything other than free software.

Can anyone advise me of the best way forward.

Thanks in anticipation.

MikeIf you have compatible versions of MSDE and SQL Server, you can detatch the msde database and reattach it to sql server without going through the export/import|||Hi

I am not sure if they are "compatable". The sql server is on myweb servers computer and I know little about it. I don't know howto attach or reattach tables but I will check on sql server 2000 bookson line.

Thanks

Mike|||

Mike,

Maybe you could import the data to a varchar column (instead of adatetime column), and write a small query to fix the formatting.

|||Hi

I have checked Books On Line and attaching a database does not sound tobe an option. I do not have access to the files of the SQLserver, but can access it through Web Data Administrator orADO.NET. Also, I want to add data to a single table withoutdisturbing the rest of my database.
Any one got any other ideas?

Mike|||Hi Geojan

That sounds an interesting idea. Not sure exactly how to do itbut I would have thought it should work OK. I could even writesome VB.NET code to do it for me (I am probably better with VB thanqueries). I am not usually quick at this sort of thing but willlet you know in the next day or two how I get on.

Thanks

Mike|||It pretty simple actually, say you have a Table (TblA), that's where you import your data, and the data column is defined as varchar(10) (named vc_Date).

And you have secound table (TblB), where the data column is defined as datetime (named dt_Date).

The sql statement needed to transfer the data from TblA to TblB, would be (Just for the example I added ColB, ColC and ColD):

Insert TabB (dt_Date, ColB, ColC, ColD)
Select Convert(datetime, Substring(Vc_Date, 7, 4) + Substring(Vc_Date, 4, 2) + Substring(Vc_Date, 1, 2))
,ColB, ColC, ColD
From TblA

If you like you can execute the statement form vb, or simply run it in query analyzer.

|||Hi Geojan

I ran the sql statement as an ExecuteNonQuery command and it worksgreat. My dates have been inserted correctly into the appropriatecolumns. Many thanks for the advice.

Mike|||

Thanks for the feedback Mike, nice to know it helped!