urgent!!! convert to datetime format from varchar...someo ne please help...

[santoshpayal]

Hi,

I have a text file that contains a date column. The text file will be
imported to database in SQL 2000 server. After to be importing, I want
to convert the date column to date type.

For ex. the text file look like

Name date
Smith 06092006 023718

It would be converted date column to ydm database in SQL 2000 server.
In the table it should look like this

Name Date
Smith 2006-09-06 hh:mm:ss.mmm

i used the query

select convert(varchar (15), getdate(), 25) from table name

which gives me the current date in the desired format...how can i replace the current date parameter to include the column 'Date'???


Much thanks in advance
appreciate if someone can help on this....

[aramki]

CODE:
select [Name],
( substring(Date, 5,4)+'-'+
substring(Date, 3,2)+'-'+
substring(Date, 1,2)+' '+
substring(Date, 10,2)+':'+
substring(Date, 12,2)+':'+
substring(Date, 14,2)) [Date]
from DateDemo

OUTPUT:
2006-09-06 02:37:18

[wallacee]

I had many dates to convert from text files in a recent data migration. I imported the text into a varchar field and created a date field to store the converted text date into. You can reformat the text and replace it into the original field, but if you want a true date field which you can do date calculations with, you must put it into a date field.

To keep your production table clean, you should do the date conversion in a temp table and only load the true date into your prod table.

HTH,

Eric