How to convert a string to timestamp of the format '30-MAR-2011 18:55:29.90'?

[former_SQLA_member1694882]

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?

[Former Member · Accepted solution]

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.

[Breck_Carter · Accepted solution]

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

[former_SQLA_member1694882 · Accepted solution]

@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

[VolkerBarth · Accepted solution]

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.

[Former Member · Accepted solution]

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.

[VolkerBarth · Accepted solution]

Time to edit the comment:)

[VolkerBarth · Accepted solution]

Is there a convert style that would fit with v11?

[Former Member · Accepted solution]

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

[Breck_Carter]

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.

[MarkCulp]

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.

[VolkerBarth]

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

[former_SQLA_member1694868]

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.

[Breck_Carter]

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

[former_SQLA_member1694868]

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