SQL Anywhere 11.01.2527

As the title asks, how can we convert a string to timestamp of the format '30-MAR-2011 18:55:29.90'?

Is there a method similar to Oracle's TO_TIMESTAMP we could use?

asked 30 Mar '11, 07:53

Nick%20Brooks's gravatar image

Nick Brooks
513172031
accept rate: 50%


I think the question was how to convert a string to a timestamp, not the reverse. The following works for the constant string provided:

select cast('30-MAR-2011 18:55:29.90' as timestamp)

You might also look at the CONVERT function, which allows a format-style value to provided to indicate the expected format of the string.

permanent link

answered 30 Mar '11, 12:34

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
accept rate: 48%

1

Ha ha... Bruce actually took the time to read the question, how come he only gets three votes versus five for Mister Opposite? :)

(30 Mar '11, 15:09) Breck Carter
Replies hidden

@Bruce - The problem with this particular example is that as is cast does not work as date_order option is set to 'MDY' instead of 'DMY'. As with Convert, I cannot find any format_style code that matches the particular format in the example.

We were trying to use datetime(...) function originally but it wasn't working, but discovered it was only due to date_order option not being set correctly. Running the following worked:

set temporary option date_order = 'DMY';

select datetime('30-MAR-2011 18:55:29.90') as it_works

(30 Mar '11, 19:41) Nick Brooks
Replies hidden

I can confirm that no convert format style seems to work with this input format in V 11.0.1.2527 - and it seems that this is independent of the date_order option.

However, when using CAST(), it seems appropriate IMHO that this does work when using date_order = 'DMY' and does not in other cases - the string is in DMY format obviously.

Therefore I would claim that using the temporary option is a correct solution.

(31 Mar '11, 03:34) Volker Barth

I did not try earlier with 11.0.1; changing the date_order option in that version is required. In version 12, regardless of the date_order setting, the string converts correctly.

(31 Mar '11, 09:21) Bruce Hay

Time to edit the comment:)

(31 Mar '11, 09:33) Volker Barth

Is there a convert style that would fit with v11?

(31 Mar '11, 09:34) Volker Barth

The format code 113 is the closest one I can see, but requires that the hyphens be replaced with spaces.

(31 Mar '11, 11:21) Bruce Hay
showing 2 of 7 show all flat view
SELECT DATEFORMAT ( CURRENT TIMESTAMP, 'dd-MMM-yyyy hh:nn:ss.ss' );

t
'30-MAR-2011 08:04:12.35'

Note the "nn" for minutes instead of mm, and the fact that m, mm, Mmm and MMM are all (I think) different... I pretty much always have to dig around in the Help when coding this kind of stuff, got lucky this time.

permanent link

answered 30 Mar '11, 08:04

Breck%20Carter's gravatar image

Breck Carter
25.7k427586845
accept rate: 20%

edited 30 Mar '11, 08:06

1

For those looking for the help, you can read about DATEFORMAT here and the timestamp format options (e.g. MMM dd YYY etc) here. Note these links are to the 12.0.1 docs... but they apply to 11.0.1 as well.

(30 Mar '11, 08:37) Mark Culp
Replies hidden

Hmm, an interesting doc comparison between ORACLE and SQL Anywhere - the rest is left as an exercise for the reader...

(30 Mar '11, 09:54) Volker Barth

Just FYI: Often I receive data for import in Excel format or CSV format. When the dates are formatted such as 3/9/2010 instead of 09-03-2010 (like SQL Anywhere wants for a cast), I would receive an error. Excel's US date formats do not include a format that matches SQL Anywhere. I did find however that UK and Canada and Afrikaans (included because it will be at the top of the list and easiest to pick) have a format that matches. I usually use that to try and clean it up before it comes in, then I can cast correctly as Bruce described.

permanent link

answered 30 Mar '11, 13:58

Siger%20Matt's gravatar image

Siger Matt
3.1k486493
accept rate: 13%

2

Have you tried SET TEMPORARY OPTION DATE_ORDER = 'MDY'? That controls how input dates are interpreted.

(30 Mar '11, 15:13) Breck Carter
Replies hidden
Comment Text Removed

Nope, but I will next time. Thanks for pointing that out.

(30 Mar '11, 16:17) Siger Matt
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:

×22

question asked: 30 Mar '11, 07:53

question was seen: 10,009 times

last updated: 31 Mar '11, 11:21