Conversion from mm/dd/yyyy to yyyy-mm-dd

Question

Can anyone please tell me how to convert char field in format mm/dd/yyyy(allowing for single digits in mm and dd fields) to "YYYY-MM-DD" . IF date is invalid I need to use '1900-01-01' also.

I have tried to cast as cast(string_field as date format 'YYYY-MM-DD') , but got the error as Invalid date.

Thanks

Answer 1

You can use JavaScript's native Date object.

var test = new Date ("05/23/1997");
var out = test.getUTCFullYear()+"-"+padLeft(test.getMonth()+1,2)+"-"+padLeft(test.getDate(),2);

function padLeft(nr, n, str){
    return Array(n-String(nr).length+1).join(str||'0')+nr;
}

The pad function is pulled from here.

Answer 2

Do this in a query(oracle):

SELECT TO_CHAR(
     TO_DATE('2/3/2016'
            ,'mm/dd/yyyy')
   ,'YYYY-MM-DD')
FROM DUAL;

Answer 3

In SQL Server...

select left(convert(varchar, cast('01/01/2017' as smalldatetime), 126), 10);