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.

Comparing a date to a datetime

5 posts in General Discussion Last posting was on 2013-02-05 14:56:26.0Z
John Flynn Posted on 2013-01-17 15:51:53.0Z
Reply-To: "John Flynn" <jflynn@miqs.com>
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
Subject: Comparing a date to a datetime
Lines: 1
Organization: MIQS, Inc.
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 15.4.3555.308
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3555.308
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50f81e19$1@forums-1-dub>
Date: 17 Jan 2013 07:51:53 -0800
X-Trace: forums-1-dub 1358437913 172.20.134.152 (17 Jan 2013 07:51:53 -0800)
X-Original-Trace: 17 Jan 2013 07:51:53 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31659
Article PK: 1159098

Hi,

I'm on ASE 15.0.3 ESD#4. Consider this sequence:

declare @int int, @float float
select @int=17, @float=17.9
if @int=@float print "equal" else print "not equal"

I get "not equal", as expected. The int apparently gets changed to float and
then the comparison is done. This seems consistent with the info in the
Reference Manual page called "Determining the datatype hierarchy". Float is
closer to the top of the list than is int, and datatypes convert "upward".

Now consider this:

declare @date date, @datetime datetime
select @date="1/17/13", @datetime="1/17/13 9:00"
if @date=@datetime print "equal" else print "not equal"

For this I get "equal". Apparently the datetime gets changed to date and
then the comparison is done. This seems like it should be exactly analogous
to the int/float example above, but it's not. Furthermore, this seems
inconsistent with the "datatype hierarchy" manual page, which lists datetime
closer to the top than date, implying to me that the date should get changed
to datetime (with a time portion of 00:00:00), and then the operation should
then logically result in "not equal".

Of course I can live with however ASE actually works (right or wrong), and I
know how to rewrite this code so it always does exactly what I want. But my
brain is having trouble wrapping itself around the logic in this basic
syntax. Am I right to consider this a logical discrepancy?

Thanks.
- John.


Rob V Posted on 2013-01-17 21:38:58.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Comparing a date to a datetime
References: <50f81e19$1@forums-1-dub>
In-Reply-To: <50f81e19$1@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: <50f86f72$1@forums-1-dub>
Date: 17 Jan 2013 13:38:58 -0800
X-Trace: forums-1-dub 1358458738 172.20.134.152 (17 Jan 2013 13:38:58 -0800)
X-Original-Trace: 17 Jan 2013 13:38:58 -0800, vip152.sybase.com
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31662
Article PK: 1159104


On 17-Jan-2013 16:51, John Flynn wrote:
> Hi,
>
> I'm on ASE 15.0.3 ESD#4. Consider this sequence:
>
> declare @int int, @float float
> select @int=17, @float=17.9
> if @int=@float print "equal" else print "not equal"
>
> I get "not equal", as expected. The int apparently gets changed to float
> and then the comparison is done. This seems consistent with the info in
> the Reference Manual page called "Determining the datatype hierarchy".
> Float is closer to the top of the list than is int, and datatypes
> convert "upward".
>
> Now consider this:
>
> declare @date date, @datetime datetime
> select @date="1/17/13", @datetime="1/17/13 9:00"
> if @date=@datetime print "equal" else print "not equal"
>
> For this I get "equal". Apparently the datetime gets changed to date and
> then the comparison is done. This seems like it should be exactly
> analogous to the int/float example above, but it's not. Furthermore,
> this seems inconsistent with the "datatype hierarchy" manual page, which
> lists datetime closer to the top than date, implying to me that the date
> should get changed to datetime (with a time portion of 00:00:00), and
> then the operation should then logically result in "not equal".
>
> Of course I can live with however ASE actually works (right or wrong),
> and I know how to rewrite this code so it always does exactly what I
> want. But my brain is having trouble wrapping itself around the logic in
> this basic syntax. Am I right to consider this a logical discrepancy?
>
> Thanks.
> - John.
>

Hmm... good point. I would agree with you.
Let me check with some folks in engineering...

--
HTH,

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

Certified Professional DBA for Sybase ASE, IQ, Replication Server

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

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


karthik Posted on 2013-01-21 08:19:50.0Z
Sender: 12fb.50fccd23.1804289383@sybase.com
From: Karthik
Newsgroups: sybase.public.ase.general
Subject: Re: Comparing a date to a datetime
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50fcfa26.2351.1681692777@sybase.com>
References: <50f86f72$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 21 Jan 2013 00:19:50 -0800
X-Trace: forums-1-dub 1358756390 172.20.134.41 (21 Jan 2013 00:19:50 -0800)
X-Original-Trace: 21 Jan 2013 00:19:50 -0800, 172.20.134.41
Lines: 74
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31665
Article PK: 1159179

Yes...Interesting!

Awaiting (eagerly) reply from Rob or from the engineering
team to know ther input on this topic.

Regards
Karthik

> On 17-Jan-2013 16:51, John Flynn wrote:
> > Hi,
> >
> > I'm on ASE 15.0.3 ESD#4. Consider this sequence:
> >
> > declare @int int, @float float
> > select @int=17, @float=17.9
> > if @int=@float print "equal" else print "not equal"
> >
> > I get "not equal", as expected. The int apparently gets
> > changed to float and then the comparison is done. This
> > seems consistent with the info in the Reference Manual
> > page called "Determining the datatype hierarchy". Float
> is closer to the top of the list than is int, and
> > datatypes convert "upward".
> >
> > Now consider this:
> >
> > declare @date date, @datetime datetime
> > select @date="1/17/13", @datetime="1/17/13 9:00"
> > if @date=@datetime print "equal" else print "not equal"
> >
> > For this I get "equal". Apparently the datetime gets
> > changed to date and then the comparison is done. This
> > seems like it should be exactly analogous to the
> > int/float example above, but it's not. Furthermore, this
> seems inconsistent with the "datatype hierarchy" manual
> > page, which lists datetime closer to the top than date,
> > implying to me that the date should get changed to
> > datetime (with a time portion of 00:00:00), and then the
> operation should then logically result in "not equal". >
> > Of course I can live with however ASE actually works
> > (right or wrong), and I know how to rewrite this code so
> > it always does exactly what I want. But my brain is
> > having trouble wrapping itself around the logic in this
> basic syntax. Am I right to consider this a logical
> discrepancy? >
> > Thanks.
> > - John.
> >
>
> Hmm... good point. I would agree with you.
> Let me check with some folks in engineering...
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------


Rob V Posted on 2013-02-04 21:35:59.0Z
Message-ID: <511029B9.3030608@sypron.nl>
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
To: John Flynn <jflynn@miqs.com>
Subject: Re: Comparing a date to a datetime
References: <50f81e19$1@forums-1-dub>
In-Reply-To: <50f81e19$1@forums-1-dub>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 4 Feb 2013 13:35:59 -0800
X-Trace: forums-1-dub 1360013759 172.20.134.152 (4 Feb 2013 13:35:59 -0800)
X-Original-Trace: 4 Feb 2013 13:35:59 -0800, vip152.sybase.com
Lines: 72
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31690
Article PK: 1307449

Folks, I have sorted this out after talking to ASE engineering.
Bottom line: the current behaviour is correct since it is
ANSI-compliant, but it would've been hard to know that. Let me explain...

First, the ASE documentation speaks only of datatype conversion
according to the type hierachy in the context of 'calculations'. The
unspoken assumption here is that this only applies to expressions and
comparisons for numerical datatypes.
For other datatypes, the datatype hierarchy does not apply in this way,
but the documentation does not indicate that.

Now, the ANSI SQL standard states that when comparing different
datatypes related to date or time, such as datetime vs. date, only those
components that are present in both datatypes are involved in a
comparison. So when comparing datetime and date, only the date part is
compared and the time part is ignored. This is different from what
happens when you convert from date to datetime, for example through a
variable assignment, where the time portion in datetime will then be be
initialised to 12:00 AM. Admittedly, it was obvious to conclude that
that was also how it would work for comparisons.

I have asked the documentation for the datatype hierarchy part to be
updated to reflect the above.

Regards,

Rob V.

On 17-Jan-2013 16:51, John Flynn wrote:
> Hi,
>
> I'm on ASE 15.0.3 ESD#4. Consider this sequence:
>
> declare @int int, @float float
> select @int=17, @float=17.9
> if @int=@float print "equal" else print "not equal"
>
> I get "not equal", as expected. The int apparently gets changed to float
> and then the comparison is done. This seems consistent with the info in
> the Reference Manual page called "Determining the datatype hierarchy".
> Float is closer to the top of the list than is int, and datatypes
> convert "upward".
>
> Now consider this:
>
> declare @date date, @datetime datetime
> select @date="1/17/13", @datetime="1/17/13 9:00"
> if @date=@datetime print "equal" else print "not equal"
>
> For this I get "equal". Apparently the datetime gets changed to date and
> then the comparison is done. This seems like it should be exactly
> analogous to the int/float example above, but it's not. Furthermore,
> this seems inconsistent with the "datatype hierarchy" manual page, which
> lists datetime closer to the top than date, implying to me that the date
> should get changed to datetime (with a time portion of 00:00:00), and
> then the operation should then logically result in "not equal".
>
> Of course I can live with however ASE actually works (right or wrong),
> and I know how to rewrite this code so it always does exactly what I
> want. But my brain is having trouble wrapping itself around the logic in
> this basic syntax. Am I right to consider this a logical discrepancy?
>
> Thanks.
> - John.
>


John Flynn Posted on 2013-02-05 14:56:26.0Z
Reply-To: "John Flynn" <jflynn@miqs.com>
From: "John Flynn" <jflynn@miqs.com>
Newsgroups: sybase.public.ase.general
References: <50f81e19$1@forums-1-dub> <511029B9.3030608@sypron.nl>
In-Reply-To: <511029B9.3030608@sypron.nl>
Subject: Re: Comparing a date to a datetime
Lines: 2
Organization: MIQS, Inc.
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 15.4.3555.308
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3555.308
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <51111d9a$1@forums-1-dub>
Date: 5 Feb 2013 06:56:26 -0800
X-Trace: forums-1-dub 1360076186 172.20.134.152 (5 Feb 2013 06:56:26 -0800)
X-Original-Trace: 5 Feb 2013 06:56:26 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31692
Article PK: 1307462


"Rob V" wrote in message news:511029B9.3030608@sypron.nl...
> Folks, I have sorted this out after talking to ASE engineering.

Thanks Rob!