The forum will be unavailable for maintenance at some point from Friday, April 13 at 19:00 EDT until Sunday, April 15 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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?

asked 10 Dec '12, 21:24

BillClune's gravatar image

accept rate: 0%

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)?

(11 Dec '12, 10:48) Volker Barth

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

permanent link

answered 10 Dec '12, 23:27

vhm's gravatar image

accept rate: 0%


For ambiguous dates such as 12/12/2012, the DATE_ORDER option should be set to the appropriate value:*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.

SET @date = '2/22/1988';
SELECT @date;
SET @date = '11/12/2012';
SELECT @date;


permanent link

answered 11 Dec '12, 15:40

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

...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)
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.

permanent link

answered 11 Dec '12, 06:19

Jan24's gravatar image

accept rate: 0%

I think this should work:

    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


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

, 'mm/dd/yyyy') as newMyDate

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

answered 11 Dec '12, 09:55

sybasetester's gravatar image

accept rate: 0%

edited 11 Dec '12, 12:30

Graeme%20Perrow's gravatar image

Graeme Perrow

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 10 Dec '12, 21:24

question was seen: 1,665 times

last updated: 11 Dec '12, 16:46