The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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,164 times

last updated: 11 Dec '12, 16:46