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

BillClune
46224
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

vhm
210131320
accept rate: 0%

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'
permanent link

answered 11 Dec '12, 15:40

Breck%20Carter's gravatar image

Breck Carter
27.4k424585838
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

Jan24
75348
accept rate: 0%

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
permanent link

answered 11 Dec '12, 09:55

sybasetester's gravatar image

sybasetester
513310
accept rate: 0%

edited 11 Dec '12, 12:30

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109

Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×15
×14
×13

question asked: 10 Dec '12, 21:24

question was seen: 1,222 times

last updated: 11 Dec '12, 16:46