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.

The conversion of a varchar data type to a datetime

3 posts in DataWindow Last posting was on 2009-11-24 11:21:46.0Z
©ula Milo¹ Posted on 2009-11-24 09:02:06.0Z
From: "©ula Milo¹" <sula@rps.cz>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: The conversion of a varchar data type to a datetime
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
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: <4b0ba10e$1@forums-1-dub>
Date: 24 Nov 2009 01:02:06 -0800
X-Trace: forums-1-dub 1259053326 10.22.241.152 (24 Nov 2009 01:02:06 -0800)
X-Original-Trace: 24 Nov 2009 01:02:06 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90337
Article PK: 409941

Hi all,
i have application which use DW connected through transaction object to the
MSSQL server.
The application worked without problems till now.But now i have trouble with
updating DW which contain date columns.
I think someone change something on the MSSQL server, but i cannot find
what...
Can you help me ?

the application display following error:

SQLSTATE = 22007
Microsoft OLE DB Provider for SQL Server
The conversion of a varchar data type to a datetime data type resulted in an
out-of-range value.

No changes made to database.

INSERT INTO podaci_kniha ( cislo, datum, odesilatel, obsah, predcislo,
prilohy, vyrizuje, vyrizeno, znacka, datumvyrizeno, cislopodani ) VALUES (
null , '11-24-2009 0:0:0.000', null, null, null, null, null, null, null,
null, null )


regards
Milos Sula


Jeremy Lakeman Posted on 2009-11-24 11:06:20.0Z
From: Jeremy Lakeman <jeremy.lakeman@gmail.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: The conversion of a varchar data type to a datetime
Date: Tue, 24 Nov 2009 03:06:20 -0800 (PST)
Organization: http://groups.google.com
Lines: 37
Message-ID: <e739c665-3495-4582-8c6c-6ddee08e3600@e4g2000prn.googlegroups.com>
References: <4b0ba10e$1@forums-1-dub>
NNTP-Posting-Host: 219.90.188.8
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1259060781 22068 127.0.0.1 (24 Nov 2009 11:06:21 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 24 Nov 2009 11:06:21 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: e4g2000prn.googlegroups.com; posting-host=219.90.188.8; posting-account=euaBtgoAAAC_vDxfsxmpMKlBxHaHpmRS
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.1.5) Gecko/20091102 Firefox/3.5.5 (.NET CLR 3.5.30729),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!nx02.iad01.newshosting.com!newshosting.com!69.16.185.11.MISMATCH!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!e4g2000prn.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90339
Article PK: 409943


On Nov 24, 7:02 pm, "©ula Milo¹" <s...@rps.cz> wrote:
> Hi all,
> i have application which use DW connected through transaction object to the
> MSSQL server.
> The application worked without problems till now.But now i have trouble with
> updating DW which contain date columns.
> I think someone change something on the MSSQL server, but i cannot find
> what...
> Can you help me ?
>
> the application display following error:
>
> SQLSTATE = 22007
> Microsoft OLE DB Provider for SQL Server
> The conversion of a varchar data type to a datetime data type resulted in an
> out-of-range value.
>
> No changes made to database.
>
> INSERT INTO podaci_kniha ( cislo, datum, odesilatel, obsah, predcislo,
> prilohy, vyrizuje, vyrizeno, znacka, datumvyrizeno, cislopodani ) VALUES (
> null , '11-24-2009 0:0:0.000', null, null, null, null, null, null, null,
> null, null )
>
> regards
> Milos Sula

From memory MSS & PB use regional settings and / or connection
properties to determine how to convert dates to / from strings.

You might need to execute a set statement on your transaction to force
the dateformat to a specific value;
http://msdn.microsoft.com/en-us/library/ms189491.aspx


Andrea Rimicci Posted on 2009-11-24 11:21:46.0Z
From: Andrea Rimicci <see.my.signature@ellesnc.com>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: The conversion of a varchar data type to a datetime
Organization: Elle s.n.c.
Message-ID: <53gng5hm5if9ure00e6nf1a8c400d23m9t@4ax.com>
References: <4b0ba10e$1@forums-1-dub>
X-Newsreader: Forte Agent 3.3/32.846
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 24 Nov 2009 03:21:46 -0800
X-Trace: forums-1-dub 1259061706 10.22.241.152 (24 Nov 2009 03:21:46 -0800)
X-Original-Trace: 24 Nov 2009 03:21:46 -0800, vip152.sybase.com
Lines: 10
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:90340
Article PK: 409945


>INSERT INTO podaci_kniha ( cislo, datum, odesilatel, obsah, predcislo,
>prilohy, vyrizuje, vyrizeno, znacka, datumvyrizeno, cislopodani ) VALUES (
>null , '11-24-2009 0:0:0.000', null, null, null, null, null, null, null,
>null, null )

As long you are targetting MSSQL, you may want to directly convert to
datetime your strings, so your code will go thru any default regional
setting used:

CONVERT(datetime, '11-24-2009 0:0:0.000', 101)