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