Convert text 2/22/1988' to date format (mm/dd/yyyy)

[Former Member]

Is there any easy function that will allow me to take a field imported into long varchar field and move it in a sql command to field defined as date?

[VolkerBarth]

To clarify: Do you know the order of these date strings (say, MDY as in the title - or YMD) or are you asking for a general purpose method to accept any order (unless it's ambiguous, apparently)?

[Breck_Carter]

The DATE_ORDER only specifies how string values are to be interpreted, not how they are to be stored... so you can temporarily set DATE_ORDER to match the string values you are dealing with and just use SET to convert... no messing with CAST required, no DATEFORMAT (which is used for output, not input), no complex code.

BEGIN
DECLARE @date DATE;
SET TEMPORARY OPTION DATE_ORDER = 'MDY';
SET @date = '2/22/1988';
SELECT @date;
SET @date = '11/12/2012';
SELECT @date;
END;

@date
'1988-02-22'

@date
'2012-11-12'

[VolkerBarth]

...and the same would work for DATETIME (aka TIMESTAMP) data types, too.

And note, the implicit conversion is tolerant w.r.t. leading zeroes and is rather flexible w.r.t. the delimiter, i.e. the following (partly uncommon) date strings would be converted, too:

SET @date = '02/22/1988';
SET @date = '2-22-1988';
SET @date = '2 22 1988';
SET @date = '2.22.1988';
SET @date = '2\\22\\1988';
SET @date = '2_22_1988';

So no need to messing around with character tests...

[Former Member]

you can use CAST function to convert as date for example select CAST ( expression AS date ) where expression could be your field name

[johnsmirnios]

For ambiguous dates such as 12/12/2012, the DATE_ORDER option should be set to the appropriate value: http://dcx.sybase.com/index.html#1201/en/dbadmin/date-order-option.html*d5e36656

[johnsmirnios]

Oops. Strictly speaking, 12/12/2012 isn't ambiguous but you know what I mean... 11/12/2012 is ambiguous.

[jan24]

Using CAST function depends on the database settings (date_format and date_order). You can use for example this command:

select cast(list(D.row_value, '-' order by X.line_num) as date)
from sa_split_list('2/22/1988', '/') D join sa_split_list('3,1,2') X on (X.row_value=D.line_num)

Where X defines the output sequence for CAST function and actual database "date_order" option.

[Former Member]

I think this should work:

SELECT DATEFORMAT(
    case when substr(myDate,2,1)!='/' then

        -- 1m-dd-yyyy
        substr(myDate,7,4)||'/'    --year
        ||substr(myDate,1,2)       --month
        ||'/'|| substr(myDate,4,2) --day

    else

        -- m-dd-yyyy
        substr(myDate,6,4)||'/'    --year
        ||'0'|| substr(myDate,1,1) --month
        ||'/'|| substr(myDate,3,2) --day
    end

, 'mm/dd/yyyy') as newMyDate

FROM( SELECT '12/22/1988' myDate ) r