|
you can use CAST function to convert as date for example select CAST ( expression AS date ) where expression could be your field name 2
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
(11 Dec '12, 05:41)
John Smirnios
Replies hidden
Oops. Strictly speaking, 12/12/2012 isn't ambiguous but you know what I mean... 11/12/2012 is ambiguous.
(11 Dec '12, 09:58)
John Smirnios
|
|
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' ...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...
(11 Dec '12, 16:45)
Volker Barth
|
|
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) Where X defines the output sequence for CAST function and actual database "date_order" option. |
|
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 |
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)?