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.

How to convert a time string with 60 in the second field

8 posts in General Discussion Last posting was on 2010-04-08 20:03:45.0Z
Gary Fu Posted on 2010-04-08 14:53:51.0Z
From: Gary Fu <gary.fu@sigmaspace.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090822)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: How to convert a time string with 60 in the second field
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bbdedff$1@forums-1-dub>
Date: 8 Apr 2010 07:53:51 -0700
X-Trace: forums-1-dub 1270738431 10.22.241.152 (8 Apr 2010 07:53:51 -0700)
X-Original-Trace: 8 Apr 2010 07:53:51 -0700, vip152.sybase.com
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29108
Article PK: 78347

Hello,

[54] MODOPS4.modaps_ops4.1> select @@version;

Adaptive Server
Enterprise/12.5.3/EBF 13204 ESD#6/P/Linux Intel/Enterprise
Linux/ase1253/1945/32-bit/OPT/Thu Jan
19 22:46:02 2006


[55] MODOPS4.modaps_ops4.1> select convert(datetime, '2009-01-25 15:47:60');
Msg 247, Level 16, State 1
Server 'MODOPS4', Line 1
Arithmetic overflow during explicit conversion of VARCHAR value
'2009-01-25 15:47:60' to a DATETIME field .
Arithmetic overflow occurred.

Is there a simple way in sql to round the 60 second to the next minute ?

Thanks,
Gary


Rob V [ Sybase ] Posted on 2010-04-08 15:58:18.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4bbdedff$1@forums-1-dub>
Subject: Re: How to convert a time string with 60 in the second field
Lines: 52
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
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: <4bbdfd1a@forums-1-dub>
Date: 8 Apr 2010 08:58:18 -0700
X-Trace: forums-1-dub 1270742298 10.22.241.152 (8 Apr 2010 08:58:18 -0700)
X-Original-Trace: 8 Apr 2010 08:58:18 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29109
Article PK: 78346

First use str_replace() to change the ':60' to ':59' (NB: make sure you're
changing the seconds, not the minutes if those could be '60' too, so you may
need to cut up the datatime string first, the use str_replace() on the
seconds part, then concate the two parts again), then use convert() to turn
it into a datetime dataype, and then use dateadd() to add 1 second. You
*can* do all this in a single expression if you want.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

"Gary Fu" <gary.fu@sigmaspace.com> wrote in message
news:4bbdedff$1@forums-1-dub...
> Hello,
>
> [54] MODOPS4.modaps_ops4.1> select @@version;
>
> Adaptive Server
> Enterprise/12.5.3/EBF 13204 ESD#6/P/Linux Intel/Enterprise
> Linux/ase1253/1945/32-bit/OPT/Thu Jan
> 19 22:46:02 2006
>
> [55] MODOPS4.modaps_ops4.1> select convert(datetime, '2009-01-25
> 15:47:60');
> Msg 247, Level 16, State 1
> Server 'MODOPS4', Line 1
> Arithmetic overflow during explicit conversion of VARCHAR value
> '2009-01-25 15:47:60' to a DATETIME field .
> Arithmetic overflow occurred.
>
> Is there a simple way in sql to round the 60 second to the next minute ?
>
> Thanks,
> Gary


Miguel X Posted on 2010-04-08 16:23:20.0Z
Sender: 17f.4bbd84b8.1804289383@sybase.com
From: Miguel X
Newsgroups: sybase.public.ase.general
Subject: Re: How to convert a time string with 60 in the second field
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bbe02f8.1a31.1681692777@sybase.com>
References: <4bbdfd1a@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Apr 2010 09:23:20 -0700
X-Trace: forums-1-dub 1270743800 10.22.241.41 (8 Apr 2010 09:23:20 -0700)
X-Original-Trace: 8 Apr 2010 09:23:20 -0700, 10.22.241.41
Lines: 67
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29110
Article PK: 78349

I have been reading the contents of the forums for a while
so I thought I will add something for once.

select dateadd(ss,60,convert(datetime, substring('2009-01-25
15:47:60', 1, char_length('2009-01-25 15:47:60') -2) +
'00'))

Ciao for now,
Miguel X

> First use str_replace() to change the ':60' to ':59' (NB:
> make sure you're changing the seconds, not the minutes if
> those could be '60' too, so you may need to cut up the
> datatime string first, the use str_replace() on the
> seconds part, then concate the two parts again), then use
> convert() to turn it into a datetime dataype, and then
> use dateadd() to add 1 second. You *can* do all this in a
> single expression if you want.
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
> Reference Guide" "The Complete Sybase Replication Server
> Quick Reference Guide"
>
> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
> http://www.sypron.nl
> Sypron B.V., Amersfoort, The Netherlands
> Chamber of Commerce 27138666
> ----------------------------------------------------------
> -------
>
> "Gary Fu" <gary.fu@sigmaspace.com> wrote in message
> news:4bbdedff$1@forums-1-dub...
> > Hello,
> >
> > [54] MODOPS4.modaps_ops4.1> select @@version;
> >
> >
> > Adaptive Server Enterprise/12.5.3/EBF 13204
> > ESD#6/P/Linux Intel/Enterprise
> > Linux/ase1253/1945/32-bit/OPT/Thu Jan 19 22:46:02 2006
> >
> > [55] MODOPS4.modaps_ops4.1> select convert(datetime,
> > '2009-01-25 15:47:60');
> > Msg 247, Level 16, State 1
> > Server 'MODOPS4', Line 1
> > Arithmetic overflow during explicit conversion of
> > VARCHAR value '2009-01-25 15:47:60' to a DATETIME field
> > . Arithmetic overflow occurred.
> >
> > Is there a simple way in sql to round the 60 second to
> the next minute ? >
> > Thanks,
> > Gary
>
>


Gary Fu Posted on 2010-04-08 17:00:15.0Z
From: Gary Fu <gary.fu@sigmaspace.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090822)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to convert a time string with 60 in the second field
References: <4bbdfd1a@forums-1-dub> <4bbe02f8.1a31.1681692777@sybase.com>
In-Reply-To: <4bbe02f8.1a31.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bbe0b9f$1@forums-1-dub>
Date: 8 Apr 2010 10:00:15 -0700
X-Trace: forums-1-dub 1270746015 10.22.241.152 (8 Apr 2010 10:00:15 -0700)
X-Original-Trace: 8 Apr 2010 10:00:15 -0700, vip152.sybase.com
Lines: 76
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29111
Article PK: 78348

Thanks for all the replies.

I learned (not in detail) it from our project that to have the 60 for
the second is for the leap second purpose. Also I tried on Postgresql
and it has no problem to handle this case to convert the 60 'second' to
the next minute. Will Sybase take care of this in the future release ?

Thanks,
Gary

> I have been reading the contents of the forums for a while
> so I thought I will add something for once.
>
> select dateadd(ss,60,convert(datetime, substring('2009-01-25
> 15:47:60', 1, char_length('2009-01-25 15:47:60') -2) +
> '00'))
>
> Ciao for now,
> Miguel X
>
>> First use str_replace() to change the ':60' to ':59' (NB:
>> make sure you're changing the seconds, not the minutes if
>> those could be '60' too, so you may need to cut up the
>> datatime string first, the use str_replace() on the
>> seconds part, then concate the two parts again), then use
>> convert() to turn it into a datetime dataype, and then
>> use dateadd() to add 1 second. You *can* do all this in a
>> single expression if you want.
>>
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE
>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>> Reference Guide" "The Complete Sybase Replication Server
>> Quick Reference Guide"
>>
>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>> http://www.sypron.nl
>> Sypron B.V., Amersfoort, The Netherlands
>> Chamber of Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>
>> "Gary Fu" <gary.fu@sigmaspace.com> wrote in message
>> news:4bbdedff$1@forums-1-dub...
>>> Hello,
>>>
>>> [54] MODOPS4.modaps_ops4.1> select @@version;
>>>
>>>
>>> Adaptive Server Enterprise/12.5.3/EBF 13204
>>> ESD#6/P/Linux Intel/Enterprise
>>> Linux/ase1253/1945/32-bit/OPT/Thu Jan 19 22:46:02 2006
>>>
>>> [55] MODOPS4.modaps_ops4.1> select convert(datetime,
>>> '2009-01-25 15:47:60');
>>> Msg 247, Level 16, State 1
>>> Server 'MODOPS4', Line 1
>>> Arithmetic overflow during explicit conversion of
>>> VARCHAR value '2009-01-25 15:47:60' to a DATETIME field
>>> . Arithmetic overflow occurred.
>>>
>>> Is there a simple way in sql to round the 60 second to
>> the next minute ? >
>>> Thanks,
>>> Gary
>>


Rob V [ Sybase ] Posted on 2010-04-08 17:14:13.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4bbdfd1a@forums-1-dub> <4bbe02f8.1a31.1681692777@sybase.com> <4bbe0b9f$1@forums-1-dub>
Subject: Re: How to convert a time string with 60 in the second field
Lines: 88
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
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: <4bbe0ee5@forums-1-dub>
Date: 8 Apr 2010 10:14:13 -0700
X-Trace: forums-1-dub 1270746853 10.22.241.152 (8 Apr 2010 10:14:13 -0700)
X-Original-Trace: 8 Apr 2010 10:14:13 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29112
Article PK: 78350

Do you happen to know if other databases (Oracle, MS SQL, etc) support this
as well?

Rob V.

"Gary Fu" <gary.fu@sigmaspace.com> wrote in message
news:4bbe0b9f$1@forums-1-dub...
> Thanks for all the replies.
>
> I learned (not in detail) it from our project that to have the 60 for the
> second is for the leap second purpose. Also I tried on Postgresql and it
> has no problem to handle this case to convert the 60 'second' to the next
> minute. Will Sybase take care of this in the future release ?
>
> Thanks,
> Gary
>
>> I have been reading the contents of the forums for a while
>> so I thought I will add something for once.
>>
>> select dateadd(ss,60,convert(datetime, substring('2009-01-25
>> 15:47:60', 1, char_length('2009-01-25 15:47:60') -2) +
>> '00'))
>>
>> Ciao for now,
>> Miguel X
>>
>>> First use str_replace() to change the ':60' to ':59' (NB:
>>> make sure you're changing the seconds, not the minutes if
>>> those could be '60' too, so you may need to cut up the
>>> datatime string first, the use str_replace() on the
>>> seconds part, then concate the two parts again), then use
>>> convert() to turn it into a datetime dataype, and then
>>> use dateadd() to add 1 second. You *can* do all this in a
>>> single expression if you want.
>>>
>>> HTH,
>>>
>>> Rob V.
>>> ----------------------------------------------------------
>>> ------- Rob Verschoor
>>>
>>> Certified Sybase Professional DBA for ASE
>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>>> 15.0.1/12.5 // TeamSybase
>>>
>>> Author of Sybase books (order online at
>>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>>> Reference Guide" "The Complete Sybase Replication Server
>>> Quick Reference Guide"
>>>
>>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>>> http://www.sypron.nl
>>> Sypron B.V., Amersfoort, The Netherlands
>>> Chamber of Commerce 27138666
>>> ----------------------------------------------------------
>>> -------
>>>
>>> "Gary Fu" <gary.fu@sigmaspace.com> wrote in message
>>> news:4bbdedff$1@forums-1-dub...
>>>> Hello,
>>>>
>>>> [54] MODOPS4.modaps_ops4.1> select @@version;
>>>>
>>>>
>>>> Adaptive Server Enterprise/12.5.3/EBF 13204
>>>> ESD#6/P/Linux Intel/Enterprise
>>>> Linux/ase1253/1945/32-bit/OPT/Thu Jan 19 22:46:02 2006
>>>>
>>>> [55] MODOPS4.modaps_ops4.1> select convert(datetime,
>>>> '2009-01-25 15:47:60');
>>>> Msg 247, Level 16, State 1
>>>> Server 'MODOPS4', Line 1
>>>> Arithmetic overflow during explicit conversion of
>>>> VARCHAR value '2009-01-25 15:47:60' to a DATETIME field
>>>> . Arithmetic overflow occurred.
>>>>
>>>> Is there a simple way in sql to round the 60 second to
>>> the next minute ? >
>>>> Thanks,
>>>> Gary
>>>


Gary Fu Posted on 2010-04-08 17:39:00.0Z
From: Gary Fu <gary.fu@sigmaspace.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090822)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to convert a time string with 60 in the second field
References: <4bbdfd1a@forums-1-dub> <4bbe02f8.1a31.1681692777@sybase.com> <4bbe0b9f$1@forums-1-dub> <4bbe0ee5@forums-1-dub>
In-Reply-To: <4bbe0ee5@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bbe14b4$1@forums-1-dub>
Date: 8 Apr 2010 10:39:00 -0700
X-Trace: forums-1-dub 1270748340 10.22.241.152 (8 Apr 2010 10:39:00 -0700)
X-Original-Trace: 8 Apr 2010 10:39:00 -0700, vip152.sybase.com
Lines: 91
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29113
Article PK: 78351

No, I don't have access to those dbs.

Gary

> Do you happen to know if other databases (Oracle, MS SQL, etc) support this
> as well?
>
> Rob V.
>
>
>
>
> "Gary Fu" <gary.fu@sigmaspace.com> wrote in message
> news:4bbe0b9f$1@forums-1-dub...
>> Thanks for all the replies.
>>
>> I learned (not in detail) it from our project that to have the 60 for the
>> second is for the leap second purpose. Also I tried on Postgresql and it
>> has no problem to handle this case to convert the 60 'second' to the next
>> minute. Will Sybase take care of this in the future release ?
>>
>> Thanks,
>> Gary
>>
>>> I have been reading the contents of the forums for a while
>>> so I thought I will add something for once.
>>>
>>> select dateadd(ss,60,convert(datetime, substring('2009-01-25
>>> 15:47:60', 1, char_length('2009-01-25 15:47:60') -2) +
>>> '00'))
>>>
>>> Ciao for now,
>>> Miguel X
>>>
>>>> First use str_replace() to change the ':60' to ':59' (NB:
>>>> make sure you're changing the seconds, not the minutes if
>>>> those could be '60' too, so you may need to cut up the
>>>> datatime string first, the use str_replace() on the
>>>> seconds part, then concate the two parts again), then use
>>>> convert() to turn it into a datetime dataype, and then
>>>> use dateadd() to add 1 second. You *can* do all this in a
>>>> single expression if you want.
>>>>
>>>> HTH,
>>>>
>>>> Rob V.
>>>> ----------------------------------------------------------
>>>> ------- Rob Verschoor
>>>>
>>>> Certified Sybase Professional DBA for ASE
>>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>>>> 15.0.1/12.5 // TeamSybase
>>>>
>>>> Author of Sybase books (order online at
>>>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>>>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>>>> Reference Guide" "The Complete Sybase Replication Server
>>>> Quick Reference Guide"
>>>>
>>>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>>>> http://www.sypron.nl
>>>> Sypron B.V., Amersfoort, The Netherlands
>>>> Chamber of Commerce 27138666
>>>> ----------------------------------------------------------
>>>> -------
>>>>
>>>> "Gary Fu" <gary.fu@sigmaspace.com> wrote in message
>>>> news:4bbdedff$1@forums-1-dub...
>>>>> Hello,
>>>>>
>>>>> [54] MODOPS4.modaps_ops4.1> select @@version;
>>>>>
>>>>>
>>>>> Adaptive Server Enterprise/12.5.3/EBF 13204
>>>>> ESD#6/P/Linux Intel/Enterprise
>>>>> Linux/ase1253/1945/32-bit/OPT/Thu Jan 19 22:46:02 2006
>>>>>
>>>>> [55] MODOPS4.modaps_ops4.1> select convert(datetime,
>>>>> '2009-01-25 15:47:60');
>>>>> Msg 247, Level 16, State 1
>>>>> Server 'MODOPS4', Line 1
>>>>> Arithmetic overflow during explicit conversion of
>>>>> VARCHAR value '2009-01-25 15:47:60' to a DATETIME field
>>>>> . Arithmetic overflow occurred.
>>>>>
>>>>> Is there a simple way in sql to round the 60 second to
>>>> the next minute ? >
>>>>> Thanks,
>>>>> Gary
>
>


George Brink Posted on 2010-04-08 19:14:41.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Thunderbird 2.0.0.24 (Windows/20100228)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to convert a time string with 60 in the second field
References: <4bbdfd1a@forums-1-dub> <4bbe02f8.1a31.1681692777@sybase.com> <4bbe0b9f$1@forums-1-dub> <4bbe0ee5@forums-1-dub>
In-Reply-To: <4bbe0ee5@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bbe2b21@forums-1-dub>
Date: 8 Apr 2010 12:14:41 -0700
X-Trace: forums-1-dub 1270754081 10.22.241.152 (8 Apr 2010 12:14:41 -0700)
X-Original-Trace: 8 Apr 2010 12:14:41 -0700, vip152.sybase.com
Lines: 92
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29114
Article PK: 78353

I have right now: MS SQL 2008, SA 11, SQLite 3.6.19.
None of them do the conversion of 01:60 to 02:00.

Rob V [ Sybase ] wrote:
> Do you happen to know if other databases (Oracle, MS SQL, etc) support this
> as well?
>
> Rob V.
>
>
>
>
> "Gary Fu" <gary.fu@sigmaspace.com> wrote in message
> news:4bbe0b9f$1@forums-1-dub...
>> Thanks for all the replies.
>>
>> I learned (not in detail) it from our project that to have the 60 for the
>> second is for the leap second purpose. Also I tried on Postgresql and it
>> has no problem to handle this case to convert the 60 'second' to the next
>> minute. Will Sybase take care of this in the future release ?
>>
>> Thanks,
>> Gary
>>
>>> I have been reading the contents of the forums for a while
>>> so I thought I will add something for once.
>>>
>>> select dateadd(ss,60,convert(datetime, substring('2009-01-25
>>> 15:47:60', 1, char_length('2009-01-25 15:47:60') -2) +
>>> '00'))
>>>
>>> Ciao for now,
>>> Miguel X
>>>
>>>> First use str_replace() to change the ':60' to ':59' (NB:
>>>> make sure you're changing the seconds, not the minutes if
>>>> those could be '60' too, so you may need to cut up the
>>>> datatime string first, the use str_replace() on the
>>>> seconds part, then concate the two parts again), then use
>>>> convert() to turn it into a datetime dataype, and then
>>>> use dateadd() to add 1 second. You *can* do all this in a
>>>> single expression if you want.
>>>>
>>>> HTH,
>>>>
>>>> Rob V.
>>>> ----------------------------------------------------------
>>>> ------- Rob Verschoor
>>>>
>>>> Certified Sybase Professional DBA for ASE
>>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>>>> 15.0.1/12.5 // TeamSybase
>>>>
>>>> Author of Sybase books (order online at
>>>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>>>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>>>> Reference Guide" "The Complete Sybase Replication Server
>>>> Quick Reference Guide"
>>>>
>>>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>>>> http://www.sypron.nl
>>>> Sypron B.V., Amersfoort, The Netherlands
>>>> Chamber of Commerce 27138666
>>>> ----------------------------------------------------------
>>>> -------
>>>>
>>>> "Gary Fu" <gary.fu@sigmaspace.com> wrote in message
>>>> news:4bbdedff$1@forums-1-dub...
>>>>> Hello,
>>>>>
>>>>> [54] MODOPS4.modaps_ops4.1> select @@version;
>>>>>
>>>>>
>>>>> Adaptive Server Enterprise/12.5.3/EBF 13204
>>>>> ESD#6/P/Linux Intel/Enterprise
>>>>> Linux/ase1253/1945/32-bit/OPT/Thu Jan 19 22:46:02 2006
>>>>>
>>>>> [55] MODOPS4.modaps_ops4.1> select convert(datetime,
>>>>> '2009-01-25 15:47:60');
>>>>> Msg 247, Level 16, State 1
>>>>> Server 'MODOPS4', Line 1
>>>>> Arithmetic overflow during explicit conversion of
>>>>> VARCHAR value '2009-01-25 15:47:60' to a DATETIME field
>>>>> . Arithmetic overflow occurred.
>>>>>
>>>>> Is there a simple way in sql to round the 60 second to
>>>> the next minute ? >
>>>>> Thanks,
>>>>> Gary
>
>


Rob V [ Sybase ] Posted on 2010-04-08 20:03:45.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4bbdfd1a@forums-1-dub> <4bbe02f8.1a31.1681692777@sybase.com> <4bbe0b9f$1@forums-1-dub> <4bbe0ee5@forums-1-dub> <4bbe2b21@forums-1-dub>
Subject: Re: How to convert a time string with 60 in the second field
Lines: 101
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Response
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: <4bbe36a1$1@forums-1-dub>
Date: 8 Apr 2010 13:03:45 -0700
X-Trace: forums-1-dub 1270757025 10.22.241.152 (8 Apr 2010 13:03:45 -0700)
X-Original-Trace: 8 Apr 2010 13:03:45 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29115
Article PK: 78354

Thanks -- the reason I was asking is that if everyone else supports it,
it'll be easier to argue for ASE to support it too.
So far it doesn't sound like this is a common feature in database-land...

Rob V.

"George Brink" <siberianowl@yahoo.com> wrote in message
news:4bbe2b21@forums-1-dub...
>I have right now: MS SQL 2008, SA 11, SQLite 3.6.19.
> None of them do the conversion of 01:60 to 02:00.
>
>
> Rob V [ Sybase ] wrote:
>> Do you happen to know if other databases (Oracle, MS SQL, etc) support
>> this as well?
>>
>> Rob V.
>>
>>
>>
>>
>> "Gary Fu" <gary.fu@sigmaspace.com> wrote in message
>> news:4bbe0b9f$1@forums-1-dub...
>>> Thanks for all the replies.
>>>
>>> I learned (not in detail) it from our project that to have the 60 for
>>> the second is for the leap second purpose. Also I tried on Postgresql
>>> and it has no problem to handle this case to convert the 60 'second' to
>>> the next minute. Will Sybase take care of this in the future release ?
>>>
>>> Thanks,
>>> Gary
>>>
>>>> I have been reading the contents of the forums for a while
>>>> so I thought I will add something for once.
>>>>
>>>> select dateadd(ss,60,convert(datetime, substring('2009-01-25
>>>> 15:47:60', 1, char_length('2009-01-25 15:47:60') -2) +
>>>> '00'))
>>>>
>>>> Ciao for now,
>>>> Miguel X
>>>>
>>>>> First use str_replace() to change the ':60' to ':59' (NB:
>>>>> make sure you're changing the seconds, not the minutes if
>>>>> those could be '60' too, so you may need to cut up the
>>>>> datatime string first, the use str_replace() on the
>>>>> seconds part, then concate the two parts again), then use
>>>>> convert() to turn it into a datetime dataype, and then
>>>>> use dateadd() to add 1 second. You *can* do all this in a
>>>>> single expression if you want.
>>>>>
>>>>> HTH,
>>>>>
>>>>> Rob V.
>>>>> ----------------------------------------------------------
>>>>> ------- Rob Verschoor
>>>>>
>>>>> Certified Sybase Professional DBA for ASE
>>>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>>>>> 15.0.1/12.5 // TeamSybase
>>>>>
>>>>> Author of Sybase books (order online at
>>>>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>>>>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>>>>> Reference Guide" "The Complete Sybase Replication Server
>>>>> Quick Reference Guide"
>>>>>
>>>>> mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>>>>> http://www.sypron.nl
>>>>> Sypron B.V., Amersfoort, The Netherlands
>>>>> Chamber of Commerce 27138666
>>>>> ----------------------------------------------------------
>>>>> -------
>>>>>
>>>>> "Gary Fu" <gary.fu@sigmaspace.com> wrote in message
>>>>> news:4bbdedff$1@forums-1-dub...
>>>>>> Hello,
>>>>>>
>>>>>> [54] MODOPS4.modaps_ops4.1> select @@version;
>>>>>>
>>>>>>
>>>>>> Adaptive Server Enterprise/12.5.3/EBF 13204
>>>>>> ESD#6/P/Linux Intel/Enterprise
>>>>>> Linux/ase1253/1945/32-bit/OPT/Thu Jan 19 22:46:02 2006
>>>>>>
>>>>>> [55] MODOPS4.modaps_ops4.1> select convert(datetime,
>>>>>> '2009-01-25 15:47:60');
>>>>>> Msg 247, Level 16, State 1
>>>>>> Server 'MODOPS4', Line 1
>>>>>> Arithmetic overflow during explicit conversion of
>>>>>> VARCHAR value '2009-01-25 15:47:60' to a DATETIME field
>>>>>> . Arithmetic overflow occurred.
>>>>>>
>>>>>> Is there a simple way in sql to round the 60 second to
>>>>> the next minute ? >
>>>>>> Thanks,
>>>>>> Gary
>>