replace and convert

[kizziebutler]

I am doing an extraction from sql 2000 to oracle, but I have a field that contains dates, but has been defined as an char value. I would like to find all dates and null values and replace with a valid date, but I need some assistance with the replace. The datefield is a char field but contains the word 'NULL' or contains date 2005-01-02 HH:MM:SS). I would like to ensure that the date comes out in the format YYYY-MM-DD HH:MM:SS, or if it contains the word NULL replace with ''. Thank you


REPLACE(CONVERT(VARHAR19,DATEFIELD,120),'')'NULL','')

 

[jbenson001]

Are you getting an error?

 

[amrita418]

Try this:

convert(varchar(26), replace(datefield, 'NULL', ''), 120)

Regards,
AA