Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

PB11.5 rejecting dd/mm/yyyy dates as being non-US

9 posts in DataWindow Last posting was on 2008-11-16 01:15:29.0Z
wizard Posted on 2008-11-10 10:05:39.0Z
From: wizard@softwaresystems.com.au
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: PB11.5 rejecting dd/mm/yyyy dates as being non-US
Date: Mon, 10 Nov 2008 02:05:39 -0800 (PST)
Organization: http://groups.google.com
Lines: 30
Message-ID: <5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com>
NNTP-Posting-Host: 58.160.116.4
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1226311539 19426 127.0.0.1 (10 Nov 2008 10:05:39 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 10 Nov 2008 10:05:39 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: r36g2000prf.googlegroups.com; posting-host=58.160.116.4; posting-account=HlC6TQoAAADWrTKQvfgtnB769B_K_CkD
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; FDM),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!r36g2000prf.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88140
Article PK: 417395

We have migrated a PB9 app to PB11.5 with no errors. We are connecting
to SQL Server 2005 using SNC. The date is set to dd/mm/yyyy in Control
Panel. Unsder debug all dates are shown correctly as dd/mm/yyyy.
However when we update a database record from a dw setting a date,
PB11.5 returns an error: "Database Error 242
SQLSTATE= 22007 The conversion of a char data type to a datetime data
type resulted in an out-of-range datetime value." This does not happen
with every date!

On investigating using SQLpreview we found the the following date
failed: UPDATE contact_relationship SET frequency = 14,
next_call_back_date = '2008-11-23 00:00:00.000' WHERE user_id = 11 AND
people_id = 1157.

However this date did not: UPDATE contact_relationship SET frequency
= 28, next_call_back_date = '2008-12-07 00:00:00.000' WHERE user_id =
11 AND people_id = 1157

This seems crazy but it would appear that internally PB11.5 rejects
non-US dates even though the format is yyyy-mm-dd. It thinks that the
format is yyyy-dd-mm! THis occurs with all dates in the application
even entering birth dates.

If we set the date formats in DBParm, the error now reads "Incorrect
syntax near '00'. The date in the SQLpreview is not formatted as dd/mm/
yyyy.

This is a showstopper for us as we cannot update any non-US dates.

Thanks for any assistance with this. Stan


Scott Morris Posted on 2008-11-10 15:25:40.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com>
Subject: Re: PB11.5 rejecting dd/mm/yyyy dates as being non-US
Lines: 59
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49185274$1@forums-1-dub>
Date: 10 Nov 2008 07:25:40 -0800
X-Trace: forums-1-dub 1226330740 10.22.241.152 (10 Nov 2008 07:25:40 -0800)
X-Original-Trace: 10 Nov 2008 07:25:40 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88142
Article PK: 417396

Inline

<wizard@softwaresystems.com.au> wrote in message
news:5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com...
> We have migrated a PB9 app to PB11.5 with no errors. We are connecting
> to SQL Server 2005 using SNC. The date is set to dd/mm/yyyy in Control
> Panel. Unsder debug all dates are shown correctly as dd/mm/yyyy.
> However when we update a database record from a dw setting a date,
> PB11.5 returns an error: "Database Error 242
> SQLSTATE= 22007 The conversion of a char data type to a datetime data
> type resulted in an out-of-range datetime value." This does not happen
> with every date!
>
> On investigating using SQLpreview we found the the following date
> failed: UPDATE contact_relationship SET frequency = 14,
> next_call_back_date = '2008-11-23 00:00:00.000' WHERE user_id = 11 AND
> people_id = 1157.
>
> However this date did not: UPDATE contact_relationship SET frequency
> = 28, next_call_back_date = '2008-12-07 00:00:00.000' WHERE user_id =
> 11 AND people_id = 1157
>
> This seems crazy but it would appear that internally PB11.5 rejects
> non-US dates even though the format is yyyy-mm-dd. It thinks that the
> format is yyyy-dd-mm! THis occurs with all dates in the application
> even entering birth dates.

What you posted is not a PB error - it is a SQL Server error. Don't have
11.5, but 11.2 /SNC without any specific date settings in dbparm or
pbodbxxx.ini, I see PB using the timestamp syntx - e.g.,
INSERT ... VALUES ( 329936, 1, 2, 2, 10001117050, {ts '2008-11-10
00:00:00.000'}, 'C', 100.00 )

Unless something has changed in PB, it may be worth investigating how/why
your application generates sql statements using yyyy-dd-mm format when your
(sql server) connection settings indicate that you are expecting yyyy-mm-dd.
If you want to read up on the details of how sql server interprets strings
as datetime values, the following link is frequently suggesting in the MS
forms.

http://www.karaszi.com/sqlserver/info_datetime.asp

If you read closely, you will see that you can affect the interpretation of
your datetime string by setting the appropriate language for the connection
or by setting the dateformat for the connection. At the very least, you
could execute a "set dateformat ydm" immediately after connecting and that
should fix things. However, that doesn't address the fact that apparently
something somewhere has changed. The default language is specifed when you
create the user in the server. Any chance that something changed on the
server (my guess) during your migration?

> If we set the date formats in DBParm, the error now reads "Incorrect
> syntax near '00'. The date in the SQLpreview is not formatted as dd/mm/
> yyyy.

For this to be meaningful, one would need to see more of the sql statement,
as well as the dbparm setting.


Roland Smith [TeamSybase] Posted on 2008-11-10 16:33:28.0Z
From: "Roland Smith [TeamSybase]" <rsmith_at_trusthss_dot_com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com> <49185274$1@forums-1-dub>
Subject: Re: PB11.5 rejecting dd/mm/yyyy dates as being non-US
Lines: 67
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49186258$1@forums-1-dub>
Date: 10 Nov 2008 08:33:28 -0800
X-Trace: forums-1-dub 1226334808 10.22.241.152 (10 Nov 2008 08:33:28 -0800)
X-Original-Trace: 10 Nov 2008 08:33:28 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88143
Article PK: 417398

I think you hit the problem on the head. The database connection defaults to
US format. If the format for the connection is changed, the datawindow
should generate the SQL statement correctly.

"Scott Morris" <bogus@bogus.com> wrote in message
news:49185274$1@forums-1-dub...
> Inline
>
> <wizard@softwaresystems.com.au> wrote in message
> news:5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com...
>> We have migrated a PB9 app to PB11.5 with no errors. We are connecting
>> to SQL Server 2005 using SNC. The date is set to dd/mm/yyyy in Control
>> Panel. Unsder debug all dates are shown correctly as dd/mm/yyyy.
>> However when we update a database record from a dw setting a date,
>> PB11.5 returns an error: "Database Error 242
>> SQLSTATE= 22007 The conversion of a char data type to a datetime data
>> type resulted in an out-of-range datetime value." This does not happen
>> with every date!
>>
>> On investigating using SQLpreview we found the the following date
>> failed: UPDATE contact_relationship SET frequency = 14,
>> next_call_back_date = '2008-11-23 00:00:00.000' WHERE user_id = 11 AND
>> people_id = 1157.
>>
>> However this date did not: UPDATE contact_relationship SET frequency
>> = 28, next_call_back_date = '2008-12-07 00:00:00.000' WHERE user_id =
>> 11 AND people_id = 1157
>>
>> This seems crazy but it would appear that internally PB11.5 rejects
>> non-US dates even though the format is yyyy-mm-dd. It thinks that the
>> format is yyyy-dd-mm! THis occurs with all dates in the application
>> even entering birth dates.
>
> What you posted is not a PB error - it is a SQL Server error. Don't have
> 11.5, but 11.2 /SNC without any specific date settings in dbparm or
> pbodbxxx.ini, I see PB using the timestamp syntx - e.g.,
> INSERT ... VALUES ( 329936, 1, 2, 2, 10001117050, {ts '2008-11-10
> 00:00:00.000'}, 'C', 100.00 )
>
> Unless something has changed in PB, it may be worth investigating how/why
> your application generates sql statements using yyyy-dd-mm format when
> your (sql server) connection settings indicate that you are expecting
> yyyy-mm-dd. If you want to read up on the details of how sql server
> interprets strings as datetime values, the following link is frequently
> suggesting in the MS forms.
>
> http://www.karaszi.com/sqlserver/info_datetime.asp
>
> If you read closely, you will see that you can affect the interpretation
> of your datetime string by setting the appropriate language for the
> connection or by setting the dateformat for the connection. At the very
> least, you could execute a "set dateformat ydm" immediately after
> connecting and that should fix things. However, that doesn't address the
> fact that apparently something somewhere has changed. The default
> language is specifed when you create the user in the server. Any chance
> that something changed on the server (my guess) during your migration?
>
>> If we set the date formats in DBParm, the error now reads "Incorrect
>> syntax near '00'. The date in the SQLpreview is not formatted as dd/mm/
>> yyyy.
>
> For this to be meaningful, one would need to see more of the sql
> statement, as well as the dbparm setting.
>
>


wizard Posted on 2008-11-12 11:34:30.0Z
From: wizard@softwaresystems.com.au
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: PB11.5 rejecting dd/mm/yyyy dates as being non-US
Date: Wed, 12 Nov 2008 03:34:30 -0800 (PST)
Organization: http://groups.google.com
Lines: 93
Message-ID: <1fe6dc33-b885-4972-84b3-f2c99ac0b650@w24g2000prd.googlegroups.com>
References: <5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com> <49185274$1@forums-1-dub> <49186258$1@forums-1-dub>
NNTP-Posting-Host: 58.160.116.4
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1226489671 3145 127.0.0.1 (12 Nov 2008 11:34:31 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 12 Nov 2008 11:34:31 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: w24g2000prd.googlegroups.com; posting-host=58.160.116.4; posting-account=HlC6TQoAAADWrTKQvfgtnB769B_K_CkD
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; FDM),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!news.glorb.com!postnews.google.com!w24g2000prd.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88172
Article PK: 417429

On Nov 11, 3:33 am, "Roland Smith [TeamSybase]"

<rsmith_at_trusthss_dot_com> wrote:
> I think you hit the problem on the head. The database connection defaults to
> US format. If the format for the connection is changed, the datawindow
> should generate the SQL statement correctly.
>
> "Scott Morris" <bo...@bogus.com> wrote in message
>
> news:49185274$1@forums-1-dub...
>
>
>
> > Inline
>
> > <wiz...@softwaresystems.com.au> wrote in message
> >news:5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com...
> >> We have migrated a PB9 app to PB11.5 with no errors. We are connecting
> >> to SQL Server 2005 using SNC. The date is set to dd/mm/yyyy in Control
> >> Panel. Unsder debug all dates are shown correctly as dd/mm/yyyy.
> >> However when we update a database record from a dw setting a date,
> >> PB11.5 returns an error:  "Database Error 242
> >> SQLSTATE= 22007 The conversion of a char data type to a datetime data
> >> type resulted in an out-of-range datetime value." This does not happen
> >> with every date!
>
> >> On investigating using SQLpreview we found the the following date
> >> failed: UPDATE contact_relationship SET frequency = 14,
> >> next_call_back_date = '2008-11-23 00:00:00.000' WHERE user_id = 11 AND
> >> people_id = 1157.
>
> >> However this date did not:  UPDATE contact_relationship SET frequency
> >> = 28, next_call_back_date = '2008-12-07 00:00:00.000' WHERE user_id =
> >> 11 AND people_id = 1157
>
> >> This seems crazy but it would appear that internally PB11.5 rejects
> >> non-US dates even though the format is yyyy-mm-dd. It thinks that the
> >> format is yyyy-dd-mm! THis occurs with all dates in the application
> >> even entering birth dates.
>
> > What you posted is not a PB error - it is a SQL Server error.  Don't have
> > 11.5, but 11.2 /SNC without any specific date settings in dbparm or
> > pbodbxxx.ini, I see PB using the timestamp syntx - e.g.,
> > INSERT ... VALUES ( 329936, 1, 2, 2, 10001117050, {ts '2008-11-10
> > 00:00:00.000'}, 'C', 100.00 )
>
> > Unless something has changed in PB, it may be worth investigating how/why
> > your application generates sql statements using yyyy-dd-mm format when
> > your (sql server) connection settings indicate that you are expecting
> > yyyy-mm-dd. If you want to read up on the details of how sql server
> > interprets strings as datetime values, the following link is frequently
> > suggesting in the MS forms.
>
> >http://www.karaszi.com/sqlserver/info_datetime.asp
>
> > If you read closely, you will see that you can affect the interpretation
> > of your datetime string by setting the appropriate language for the
> > connection or by setting the dateformat for the connection.  At the very
> > least, you could execute a "set dateformat ydm" immediately after
> > connecting and that should fix things.  However, that doesn't address the
> > fact that apparently something somewhere has changed.  The default
> > language is specifed when you create the user in the server.  Any chance
> > that something changed on the server (my guess) during your migration?
>
> >> If we set the date formats in DBParm, the error now reads "Incorrect
> >> syntax near '00'. The date in the SQLpreview is not formatted as dd/mm/
> >> yyyy.
>
> > For this to be meaningful, one would need to see more of the sql
> > statement, as well as the dbparm setting.- Hide quoted text -
>
> - Show quoted text -

Roland,

How do I "If the format for the connection is changed" as per your
suggestion?

Regards

Stan


wizard Posted on 2008-11-12 11:33:12.0Z
From: wizard@softwaresystems.com.au
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: PB11.5 rejecting dd/mm/yyyy dates as being non-US
Date: Wed, 12 Nov 2008 03:33:12 -0800 (PST)
Organization: http://groups.google.com
Lines: 116
Message-ID: <5b74e6f1-ed58-4441-aa16-852ff6e58bf2@40g2000prx.googlegroups.com>
References: <5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com> <49185274$1@forums-1-dub>
NNTP-Posting-Host: 58.160.116.4
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1226489592 23871 127.0.0.1 (12 Nov 2008 11:33:12 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 12 Nov 2008 11:33:12 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: 40g2000prx.googlegroups.com; posting-host=58.160.116.4; posting-account=HlC6TQoAAADWrTKQvfgtnB769B_K_CkD
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; FDM),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!40g2000prx.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88171
Article PK: 417426


On Nov 11, 2:25 am, "Scott Morris" <bo...@bogus.com> wrote:
> Inline
>
> <wiz...@softwaresystems.com.au> wrote in message
>
> news:5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com...
>
>
>
>
>
> > We have migrated a PB9 app to PB11.5 with no errors. We are connecting
> > to SQL Server 2005 using SNC. The date is set to dd/mm/yyyy in Control
> > Panel. Unsder debug all dates are shown correctly as dd/mm/yyyy.
> > However when we update a database record from a dw setting a date,
> > PB11.5 returns an error:  "Database Error 242
> > SQLSTATE= 22007 The conversion of a char data type to a datetime data
> > type resulted in an out-of-range datetime value." This does not happen
> > with every date!
>
> > On investigating using SQLpreview we found the the following date
> > failed: UPDATE contact_relationship SET frequency = 14,
> > next_call_back_date = '2008-11-23 00:00:00.000' WHERE user_id = 11 AND
> > people_id = 1157.
>
> > However this date did not:  UPDATE contact_relationship SET frequency
> > = 28, next_call_back_date = '2008-12-07 00:00:00.000' WHERE user_id =
> > 11 AND people_id = 1157
>
> > This seems crazy but it would appear that internally PB11.5 rejects
> > non-US dates even though the format is yyyy-mm-dd. It thinks that the
> > format is yyyy-dd-mm! THis occurs with all dates in the application
> > even entering birth dates.
>
> What you posted is not a PB error - it is a SQL Server error.  Don't have
> 11.5, but 11.2 /SNC without any specific date settings in dbparm or
> pbodbxxx.ini, I see PB using the timestamp syntx - e.g.,
> INSERT ... VALUES ( 329936, 1, 2, 2, 10001117050, {ts '2008-11-10
> 00:00:00.000'}, 'C', 100.00 )
>
> Unless something has changed in PB, it may be worth investigating how/why
> your application generates sql statements using yyyy-dd-mm format when your
> (sql server) connection settings indicate that you are expecting yyyy-mm-dd.
> If you want to read up on the details of how sql server interprets strings
> as datetime values, the following link is frequently suggesting in the MS
> forms.
>
> http://www.karaszi.com/sqlserver/info_datetime.asp
>
> If you read closely, you will see that you can affect the interpretation of
> your datetime string by setting the appropriate language for the connection
> or by setting the dateformat for the connection.  At the very least, you
> could execute a "set dateformat ydm" immediately after connecting and that
> should fix things.  However, that doesn't address the fact that apparently
> something somewhere has changed.  The default language is specifed when you
> create the user in the server.  Any chance that something changed on the
> server (my guess) during your migration?
>
> > If we set the date formats in DBParm, the error now reads "Incorrect
> > syntax near '00'. The date in the SQLpreview is not formatted as dd/mm/
> > yyyy.
>
> For this to be meaningful, one would need to see more of the sql statement,
> as well as the dbparm setting.- Hide quoted text -
>
> - Show quoted text -

Scott,

I agree that the error message is not from PB. On the DB I have
excuted the following scripts:
SET LANGUAGE british
SET DATEFORMAT ymd

select convert(datetime,'2008-29-19' ) -- NOK returned error as
expected.

UPDATE contact_relationship SET frequency = 14, next_call_back_date =
'2008-11-26 00:00:00.000'
WHERE user_id = 11 AND people_id = 124 -- OK

The issue persited. So I executed in the app object:

EXCUTE IMMEDIATE 'SET LANGUAGE british' USING SQLCA;
EXCUTE IMMEDIATE 'SET DATEFORMAT ymd' USING SQLCA;

I deleted the DBProfile and ensured that in the connection string
under DBParm was only the database name.

How can I set the format of the date/ datetime that PB generates from
a DW? Why is it in yyyy-dd-mm format? How can I set it to be YYYYMMYY
which Karasz recommends? I recall the PB generated the date as dd/mm/
yyyy.

The DBProfile allows one to enter date, datetime and time format what
is this for? I tried setting the format to dd/mm/yyyy and got about 20
errors when starting up the application.

I cannot think of anything else to try. Thanks

Stan


Scott Morris Posted on 2008-11-12 17:25:29.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com> <49185274$1@forums-1-dub> <5b74e6f1-ed58-4441-aa16-852ff6e58bf2@40g2000prx.googlegroups.com>
Subject: Re: PB11.5 rejecting dd/mm/yyyy dates as being non-US
Lines: 39
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <491b1189$1@forums-1-dub>
Date: 12 Nov 2008 09:25:29 -0800
X-Trace: forums-1-dub 1226510729 10.22.241.152 (12 Nov 2008 09:25:29 -0800)
X-Original-Trace: 12 Nov 2008 09:25:29 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88176
Article PK: 417431


> EXCUTE IMMEDIATE 'SET LANGUAGE british' USING SQLCA;
> EXCUTE IMMEDIATE 'SET DATEFORMAT ymd' USING SQLCA;

You only need to do the latter - if that is the approach you want to take.

> I deleted the DBProfile and ensured that in the connection string
> under DBParm was only the database name.

Are you referring to the IDE profile? If so, that has no effect on your
application at runtime.

> How can I set the format of the date/ datetime that PB generates from
> a DW? Why is it in yyyy-dd-mm format? How can I set it to be YYYYMMYY
> which Karasz recommends? I recall the PB generated the date as dd/mm/
> yyyy.

That I cannot answer. No much good documentation about how PB generates
these values. Perhaps it uses the regional settings in some way? Usually I
work backwards in situations like this. Figure out what format is used by
PB and adjust "things" to make these formats work. Did the "set dateformat"
command not work? I expected that it would.

> The DBProfile allows one to enter date, datetime and time format what
> is this for? I tried setting the format to dd/mm/yyyy and got about 20
> errors when starting up the application.

It isn't clear from the documentation exactly how these values are used (and
where). In particular, I notice that PB uses two different formats in my
applications for datetime values. When using datawindows to directly
manipulate tables, PB uses the timestamp format (that I mentioned earlier)
for insert/update/delete statements. For stored procedure arguments, it
uses a different string format (e.g., 'Nov 12 2008 12:01:45:960PM'). One
last thing to consider is the pbodbxxx.ini file. Don't know if this comes
into play for SNC or if it has an effect on the formats used. This file has
always been a bit of a mystery to me so I've avoided it.


wizard Posted on 2008-11-13 10:05:57.0Z
From: wizard@softwaresystems.com.au
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: PB11.5 rejecting dd/mm/yyyy dates as being non-US
Date: Thu, 13 Nov 2008 02:05:57 -0800 (PST)
Organization: http://groups.google.com
Lines: 91
Message-ID: <ae6b69a0-53d0-444d-88a6-8f9fd44dafa9@40g2000prx.googlegroups.com>
References: <5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com> <49185274$1@forums-1-dub> <5b74e6f1-ed58-4441-aa16-852ff6e58bf2@40g2000prx.googlegroups.com> <491b1189$1@forums-1-dub>
NNTP-Posting-Host: 58.160.116.4
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1226570757 22971 127.0.0.1 (13 Nov 2008 10:05:57 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 13 Nov 2008 10:05:57 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: 40g2000prx.googlegroups.com; posting-host=58.160.116.4; posting-account=HlC6TQoAAADWrTKQvfgtnB769B_K_CkD
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; FDM),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!news.glorb.com!news2!postnews.google.com!40g2000prx.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88183
Article PK: 417437


On Nov 13, 4:25 am, "Scott Morris" <bo...@bogus.com> wrote:
> > EXCUTE IMMEDIATE 'SET LANGUAGE british' USING SQLCA;
> > EXCUTE IMMEDIATE 'SET DATEFORMAT ymd' USING SQLCA;
>
> You only need to do the latter - if that is the approach you want to take.
>
> > I deleted the DBProfile and ensured that in the connection string
> > under DBParm was only the database name.
>
> Are you referring to the IDE profile? If so, that has no effect on your
> application at runtime.
>
> > How can I set the format of the date/ datetime that PB generates from
> > a DW? Why is it in yyyy-dd-mm format? How can I set it to be YYYYMMYY
> > which Karasz recommends? I recall the PB generated the date as dd/mm/
> > yyyy.
>
> That I cannot answer.  No much good documentation about how PB generates
> these values.  Perhaps it uses the regional settings in some way?  Usually I
> work backwards in situations like this.  Figure out what format is used by
> PB and adjust "things" to make these formats work. Did the "set dateformat"
> command not work?  I expected that it would.
>
> > The DBProfile allows one to enter date, datetime and time format what
> > is this for? I tried setting the format to dd/mm/yyyy and got about 20
> > errors when starting up the application.
>
> It isn't clear from the documentation exactly how these values are used (and
> where). In particular, I notice that PB uses two different formats in my
> applications for datetime values.  When using datawindows to directly
> manipulate tables, PB uses the timestamp format (that I mentioned earlier)
> for insert/update/delete statements.  For stored procedure arguments, it
> uses a different string format (e.g., 'Nov 12 2008 12:01:45:960PM'). One
> last thing to consider is the pbodbxxx.ini file.  Don't know if this comes
> into play for SNC or if it has an effect on the formats used.  This file has
> always been a bit of a mystery to me so I've avoided it.

Scott,

Thanks for the feedback. BTW it still does not work but in the
meantime I have found:

1. My MSS SQL Server 2000 has settings: Language = us_english and
DateFormat = ymd. I have my SQL 2005 DB to these settings as it all
worked pefectly on SQL 2000.

2. SyBooks state that DateFormat is supported in SNC, However EXECUTE
IMMEDIATE 'SET DATEFORMAT ymd' USING SQLCA; fails with a message
"DataFormat is not a recognised SET option".

3. SyBooks suggest setting the date format in SQLCA as a DBParm. If I
do this my datawindow reports a different error message: "Incorrect
syntax near '00'."

4. Pasting the SQLPreview script into QueryAnalyser and execting it.
It works fine. UPDATE contact_relationship SET frequency = 14,
next_call_back_date = '2008-11-23 00:00:00.000' WHERE user_id = 11
AND
people_id = 1157

5. BTW I have tested all the valid DateFormats dmy, ymd, mdy etc and
found that ymd, mdy, myd are all the same and so are dmy, ydm, dym.
The "y" plays no part; only the sequence of dm or md in the string set
the secquence.

6. BTW according to Karaszi the ODBC format is independent of the
order: {ts '2008-11-10 00:00:00.000'},

Question 1: Am I in the right PB Group? Would the PB Connections Group
be more appropritae?
Question 2. As PB 11.5 is so new, I am the only one in a country where
the default date format is dd/mm/yyyy?

This has been an interesting exercise in understanding how SQL Server
works but not PB. However this is still a showstopper and have not
progressed an iota. Has anyone else in [Team Sybase] come across this
issue?

Regards, Stan


Scott Morris Posted on 2008-11-14 14:13:34.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com> <49185274$1@forums-1-dub> <5b74e6f1-ed58-4441-aa16-852ff6e58bf2@40g2000prx.googlegroups.com> <491b1189$1@forums-1-dub> <ae6b69a0-53d0-444d-88a6-8f9fd44dafa9@40g2000prx.googlegroups.com>
Subject: Re: PB11.5 rejecting dd/mm/yyyy dates as being non-US
Lines: 71
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <491d878e$1@forums-1-dub>
Date: 14 Nov 2008 06:13:34 -0800
X-Trace: forums-1-dub 1226672014 10.22.241.152 (14 Nov 2008 06:13:34 -0800)
X-Original-Trace: 14 Nov 2008 06:13:34 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88187
Article PK: 417441


> 1. My MSS SQL Server 2000 has settings: Language = us_english and
> DateFormat = ymd. I have my SQL 2005 DB to these settings as it all
> worked pefectly on SQL 2000.

I'm assuming that you mean the user has the default language set. Any
server or database setting is not meaningful. I'm not certain where you set
the dateformat within the server / database / user. Can you elaborate on
this? The only thing I know that affects the interpretation is the
language.

> 2. SyBooks state that DateFormat is supported in SNC, However EXECUTE
> IMMEDIATE 'SET DATEFORMAT ymd' USING SQLCA; fails with a message
> "DataFormat is not a recognised SET option".

Look closely at the error message. Did you have a typo in your execute
statement? I have no problems executing this statement.

> 3. SyBooks suggest setting the date format in SQLCA as a DBParm. If I
> do this my datawindow reports a different error message: "Incorrect
> syntax near '00'."

As I said, this area has always been a bit of a mystery to me. You may need
to experiment with the value a bit to have some (desirable) effect. It may
help to look at the insert/update statement that is executed when the error
occurs to see what effect it has on PB.

> 4. Pasting the SQLPreview script into QueryAnalyser and execting it.
> It works fine. UPDATE contact_relationship SET frequency = 14,
> next_call_back_date = '2008-11-23 00:00:00.000' WHERE user_id = 11
> AND
> people_id = 1157

Unfortunately, you need to set the connection settings in QA to those used
by your application in order for this to be meaningful. The best way to
identify those settings is to use the SQL Server Profiler tool. In fact,
that is the tool that I rely on most in situations like this.

> 5. BTW I have tested all the valid DateFormats dmy, ymd, mdy etc and
> found that ymd, mdy, myd are all the same and so are dmy, ydm, dym.
> The "y" plays no part; only the sequence of dm or md in the string set
> the secquence.

Not sure exactly what this means or how you tested. I can say that is does
affect how the DB engine interprets strings (of certain formats) as datetime
values.

> 6. BTW according to Karaszi the ODBC format is independent of the
> order: {ts '2008-11-10 00:00:00.000'},

That is not correct. Either there is a typo or a misunderstanding
somewhere. According to the MS documentation, the timestamp format is: { ts
'yyyy-mm-dd hh:mm:ss[.fff] '}

> Question 1: Am I in the right PB Group? Would the PB Connections Group
> be more appropritae?

Connections gets very little activity. This one is just as good as the
others.

> Question 2. As PB 11.5 is so new, I am the only one in a country where
> the default date format is dd/mm/yyyy?

I doubt it. My guess is that there is something specific about your
particular installation that is causing the problem. Not having 11.5, I
can't do much experimentation. One thing you may want to investigate are
the regional settings on the client computer. Try changing the date format
to see if there is any impact. Also, do you use / distribute the
pbodb115.ini file?


Stan Greene Posted on 2008-11-16 01:15:29.0Z
From: Stan Greene <wizard@softwaresystems.com.au>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: PB11.5 rejecting dd/mm/yyyy dates as being non-US
Date: Sat, 15 Nov 2008 17:15:29 -0800 (PST)
Organization: http://groups.google.com
Lines: 93
Message-ID: <6bb15329-1a94-4ff1-adce-8e569a95484d@r15g2000prh.googlegroups.com>
References: <5f90cecf-6425-4a60-a401-a641ee05178a@r36g2000prf.googlegroups.com> <49185274$1@forums-1-dub> <5b74e6f1-ed58-4441-aa16-852ff6e58bf2@40g2000prx.googlegroups.com> <491b1189$1@forums-1-dub> <ae6b69a0-53d0-444d-88a6-8f9fd44dafa9@40g2000prx.googlegroups.com> <491d878e$1@forums-1-dub>
NNTP-Posting-Host: 58.160.116.4
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1226798129 23799 127.0.0.1 (16 Nov 2008 01:15:29 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Sun, 16 Nov 2008 01:15:29 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: r15g2000prh.googlegroups.com; posting-host=58.160.116.4; posting-account=HlC6TQoAAADWrTKQvfgtnB769B_K_CkD
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; FDM),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!nntp1.roc.gblx.net!nntp.gblx.net!nntp.gblx.net!nlpi057.nbdc.sbc.com!prodigy.net!border1.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!r15g2000prh.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88190
Article PK: 417443


On Nov 15, 1:13 am, "Scott Morris" <bo...@bogus.com> wrote:
> > 1. My MSS SQL Server 2000 has settings: Language = us_english and
> > DateFormat = ymd. I have my SQL 2005 DB to these settings as it all
> > worked pefectly on SQL 2000.
>
> I'm assuming that you mean the user has the default language set.  Any
> server or database setting is not meaningful.  I'm not certain where you set
> the dateformat within the server / database / user.  Can you elaborate on
> this?  The only thing I know that affects the interpretation is the
> language.
>
> > 2. SyBooks state that DateFormat is supported in SNC, However EXECUTE
> > IMMEDIATE 'SET DATEFORMAT ymd' USING SQLCA; fails with a message
> > "DataFormat is not a recognised SET option".
>
> Look closely at the error message.  Did you have a typo in your execute
> statement?  I have no problems executing this statement.
>
> > 3. SyBooks suggest setting the date format in SQLCA as a DBParm. If I
> > do this my datawindow reports a different error message: "Incorrect
> > syntax near '00'."
>
> As I said, this area has always been a bit of a mystery to me.  You may need
> to experiment with the value a bit to have some (desirable) effect.  It may
> help to look at the insert/update statement that is executed when the error
> occurs to see what effect it has on PB.
>
> > 4. Pasting the SQLPreview script into QueryAnalyser and execting it.
> > It works fine. UPDATE contact_relationship SET frequency = 14,
> > next_call_back_date = '2008-11-23 00:00:00.000' WHERE user_id = 11
> > AND
> > people_id = 1157
>
> Unfortunately, you need to set the connection settings in QA to those used
> by your application in order for this to be meaningful.  The best way to
> identify those settings is to use the SQL Server Profiler tool.  In fact,
> that is the tool that I rely on most in situations like this.
>
> > 5. BTW I have tested all the valid DateFormats dmy, ymd, mdy etc and
> > found that ymd, mdy, myd are all the same and so are dmy, ydm, dym.
> > The "y" plays no part; only the sequence of dm or md in the string set
> > the secquence.
>
> Not sure exactly what this means or how you tested.  I can say that is does
> affect how the DB engine interprets strings (of certain formats) as datetime
> values.
>
> > 6. BTW according to Karaszi the ODBC format is independent of the
> > order: {ts '2008-11-10 00:00:00.000'},
>
> That is not correct.  Either there is a typo or a misunderstanding
> somewhere. According to the MS documentation, the timestamp format is: { ts
> 'yyyy-mm-dd hh:mm:ss[.fff] '}
>
> > Question 1: Am I in the right PB Group? Would the PB Connections Group
> > be more appropritae?
>
> Connections gets very little activity.  This one is just as good as the
> others.
>
> > Question 2. As PB 11.5 is so new, I am the only one in a country where
> > the default date format is dd/mm/yyyy?
>
> I doubt it.  My guess is that there is something specific about your
> particular installation that is causing the problem.  Not having 11.5, I
> can't do much experimentation.  One thing you may want to investigate are
> the regional settings on the client computer.  Try changing the date format
> to see if there is any impact.  Also, do you use / distribute the
> pbodb115.ini file?

I did not realise that the Language / DateFormat had to be set on a
user basis. Now when I set the Language (us_english) and DateFormat
(mdy) just after connecting to the database, it all seems to work
fine. Thanks for clarifying this for me.

BTW I did lot of testing on the DB and id find that using SET
DATEFORMAT xxx one can overwrite the default Language setting.

Many thanks for your patience.