Date format conversion

[sam12]

Hi
I am having a problem to convert the date format
This is my date feild in an existing database

6/26/02 11
05/02/2002
6/05/02 3:
6/12/02 11
05/02/2002
05/02/2002
6/06/02 10
6/06/02 10
6/06/02 10
6/06/02 10
6/06/02 10
6/06/02 10
05/02/2002

I want date in format of mm/dd/yyyy or mm/dd/yyyy hh:mm:ssss (Time doesn't matter, 12:00:AM is good )
I was trying

CONVERT(datetim e,LEFT(dbo.Orde r_Lines.BIN, CHARINDEX(' ', dbo.Order_Lines .BIN, 0)))

tablename :order_lines
filedname: bin


It works for 90% records(like 6/06/02 10). But for some records it gives no date it gives 12:00:AM only, i think when CHARINDEX(' ', dbo.Order_Lines .BIN, 0 )returns a negative value or 0.
Not sure

Thanks in advance

[Atli]

Hi.

That looks more like MSSQL than MySQL. Did you post it in the wrong forum?

I have to ask tho, why do you store dates in a string type like that?
Wouldn't it be better to use the DATE and TIME types? (Or even DateTime if that's available)
No matter which SQL server you are using, those basic types should be available.

That way all your dates would be stored in a standard format.

[sam12]

Hi.

That looks more like MSSQL than MySQL. Did you post it in the wrong forum?

I have to ask tho, why do you store dates in a string type like that?
Wouldn't it be better to use the DATE and TIME types? (Or even DateTime if that's available)
No matter which SQL server you are using, those basic types should be available.

That way all your dates would be stored in a standard format.

First of all Thanks for replying

Actually this is old database
I don't know who made it and i am having problems with this now
What i am doing is , creating another field DATE in the table
And its datatype is datetime
I want to copy the old data from BIN to DATE field
I was trying to run this script

Dim RS As New adodb.Recordset
RS.ActiveConnection = CurrentProject.Connection
RS.CursorType = adOpenDynamic
RS.LockType = adLockPessimistic
RS.Open "order_lines"


If Not RS.EOF Then
    RS.MoveFirst
    'RS1.MoveFirst
    Do While Not RS.EOF
        RS.Fields("DATE") = RS.Fields("BIN")
        RS.Update
        RS.MoveNext
   Loop
End if

But it gives error :"multiple step operation"
This is because i am trying to paste varchar format to datetime format
Is there any way to get only date from BIN field and then copy it to DATE field
I was trying split function ,but didn't work for me

Thanks again for replying

[Atli]

I'm afraid that since your old dates are stored in a VarChar field in seemingly random formats, then your only way to convert all of them would be to find the format of each one and parse them into the standard format.

There is no built in function to parse randomly formatted data into the standard format. This is why we use standard types like the Date type, so we don't run into these sort of problems