Bug in convert() function when using style 121?

[sebflaesch]

Hi all,

Using Sybase 15.5 developer edition:

According to the documentation, the convert() function supports the
style 121 (100+21) to parse strings with following format:

yyyy/mm/dd HH:mm:ss

But I get an error:

1> select convert(datetime,'2010/11/23 13:44:55',121)
2> go
Msg 249, Level 16, State 1:
Server 'ASE_ORCA', Line 1:
Syntax error during explicit conversion of VARCHAR value '2010/11/23
13:44:55'
to a DATETIME field.

Without any style-code, it gets converted:

1> select convert(datetime,'2010/11/23 13:44:55')
2> go

--------------------------
Nov 23 2010 1:44PM

(1 row affected)

So what I am doing wrong here?

Note that the style-code 121 is the same in SQL Server's convert()
function, so normally this should work, and to me it looks like a
bug...

Seb

[ckb]

Hi There,

Try select convert(varchar(50),convert(datetime,'2010/11/23
13:44:55') ,101)+' '+convert(varchar(50),convert(datetime,'2010/11/23
13:44:55') ,108)

My understanding is that a datetime is a datetime. The formatting
codes are for display purposes only. I don't know what version you
are using but 121 is not listed as a valid format code for version
12.5.

Here is a link to the books online list of valid format codes:

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookView?DwebQuery=convert

Here is a copy and paste of the table.

Converting date formats with the style parameter Without century (yy)
With century (yyyy)
Standard
Output

-
0 or 100
Default
mon dd yyyy hh:mm AM (or PM)

1
101
USA
mm/dd/yy

2
2
SQL standard
yy.mm.dd

3
103
English/French
dd/mm/yy

4
104
German
dd.mm.yy

5
105

dd-mm-yy

6
106

dd mon yy

7
107

mon dd, yy

8
108

hh:mm:ss

-
9 or 109
Default + milliseconds
mon dd yyyy hh:mm:sss AM (or PM)

10
110
USA
mm-dd-yy

11
111
Japan
yy/mm/dd

12
112
ISO
yymmdd

Hope this helps!

[PDreyer]

I don't have ASE 15.5 to test with at the moment but try code 111 i.e.
select convert(datetime,'2010/11/23 13:44:55',111)

The posts on google groups does not reach the actual newsgroup
This has been broken for some time now
Instead you can use sybase.public.ase.general newsgroup at
http://forums.sybase.com/cgi-bin/webnews.cgi?cmd=listitems&sort_on=none&second_sort=date&sort_method=date&sort_reverse=true&group=sybase.public.ase.general

[PDreyer]

On Jul 6, 1:06 pm, ckb <carolinekb...@gmail.com> wrote:
> Hi There,
>
-- snip, snip --

> My understanding is that a datetime is a datetime.  The formatting
> codes are for display purposes only.  

-- snip, snip --

No, you do need it to overide the default conversion e.g.

1> select convert(datetime,'01/02/03',1)
2> go

--------------------------
Jan 2 2003 12:00AM

(1 row affected)
1> select convert(datetime,'01/02/03',3)
2> go

--------------------------
Feb 1 2003 12:00AM

(1 row affected)
1> select convert(datetime,'01/02/03',11)
2> go

--------------------------
Feb 3 2001 12:00AM

(1 row affected)

[sebflaesch]

Thanks a lot for your answers.

The 15.5 doc says 21 is for yy/mm/dd HH:mm:ss and if you want to parse
years on 4 digits, just add 100 to the code.

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36271.1550/html/blocks/X41864.htm

"When converting datetime or smalldatetime data to a character type,
use the style numbers in Table 2-7 to specify the display format.
Values in the left-most column display 2-digit years (yy). For 4-digit
years (yyyy), add 100, or use the value in the middle column."

Doc writers: It would be easier to read if the doc would just list the
actual codes for yyyy in the "middle column" (i.e. with the title
"With century (yyyy)")

As I wrote in the first post, I am quite sure about code 121 because
this is what I use with SQL Server.
(from my understanding, Sybase ASE convert() function is compatible
with the SQL Server function)

This looks like a bug to me.

Seb

[sebflaesch]

My Sybase support contact told me that it's a documentation bug, the
style 121 is not supported.
Seb