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.

Question about date ranges in SQL

3 posts in Windows NT Last posting was on 2000-08-23 15:27:06.0Z
jthornton Posted on 2000-08-22 15:29:16.0Z
From: jthornton@cotton.org
Date: Tue, 22 Aug 2000 11:29:16 -0400
Newsgroups: sybase.public.sqlserver.nt
Subject: Question about date ranges in SQL
Message-ID: <2AECD03E73C94811005513A985256943.005513B785256943@webforums>
Lines: 41
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:1716
Article PK: 1089191

I'm running ASE 11.9.2 on Windows NT 4.0 and have an interesting issue...

We have an application written by a vendor in 1999 that contains a query
based on a range of dates. In the following queries,

fut_date is a datetime column
fut_code is a char(5) column

The first query asks for a data in 1999 and provides correct results.

select * from futures
where fut_date >= '8/2/99'
and fut_date <= '8/18/99'
and fut_code = 'CTV99'

The second query (below) asks for data in a range for 2000.

select * from futures
where fut_date >= '8/1/00'
and fut_date <= '8/18/00'
and fut_code = 'CTV00'

The results do not include the ending date (where fut_date = '8/18/00').
Its not a data issue because I can query for specifically the data on
8/18/00 where fut_code = 'CTV00' and I get back correct results. I can
also leave out the ending date clause in this query so that the query
returns data >= '8/1/00' and fut_code = 'CTV00' and this also returns
correct results. However, the application needs to be able to return data
for a range. I understand that BETWEEN can be used, but the query above
should also work...

Can anyone see anything wrong with the second query? Is there a reason why
the same query would work correctly in 1999 but not in 2000?

Any help would be tremendous!

Thanks in Advance!

Jim Thornton
jthornton@cotton.org
National Cotton Council


"Mark A. Parsons" <pegasys Posted on 2000-08-22 23:23:43.0Z
Message-ID: <39A30B7E.5F5463C6@_internet.co.nz>
Date: Wed, 23 Aug 2000 11:23:43 +1200
From: "Mark A. Parsons" <pegasys@_internet.co.nz>
Organization: Pegasys (2000) Limited
X-Mailer: Mozilla 4.72 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Question about date ranges in SQL
References: <2AECD03E73C94811005513A985256943.005513B785256943@webforums>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 61
NNTP-Posting-Host: p402.ipa1-n8-16.iconz.net.nz 210.48.25.146
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1712
Article PK: 1089185


> The results do not include the ending date (where fut_date = '8/18/00').
> Its not a data issue because I can query for specifically the data on
> 8/18/00 where fut_code = 'CTV00' and I get back correct results. I can
> also leave out the ending date clause in this query so that the query
> returns data >= '8/1/00' and fut_code = 'CTV00' and this also returns
> correct results. However, the application needs to be able to return data
> for a range. I understand that BETWEEN can be used, but the query above
> should also work...
>
> Can anyone see anything wrong with the second query? Is there a reason why
> the same query would work correctly in 1999 but not in 2000?

The 'datetime' and 'smalldatetime' columns include both date *and* time.

If you use getdate() to populate a column with an insert/update ... then the
column will contain the 'current' date and time.

If you happen to insert/update the column with a string like '8/18/00' ...
you've supplied the date portion but Sybase still needs the time portion. In
this case Sybase will default the time to 12:00am.

When you run a search on a small/datetime column you have to supply both the
date *and* time. Just like with the insert/update scenario ... if you leave
off the time component Sybase will default it to 12:00am.

Now, back to your issue ...

Let's say you have some data in your table that was created on 8/18/00 at
3:39pm ... this would be stored in the database as '8/18/00 3:39pm' (ignore
formatting for now).

If you were to search on this column like you do in your query ... "where
fut_date <= '8/18/00' " ... Sybase will tack on the 12:00am thus giving you a
search of ... "where fut_date <= '8/18/00 12:00am' ". Since 12:00am is less
than 3:39pm ... you won't see your data in this particular result set.

NOTE: For the beginning of date ranges the 12:00am works just fine since
'12:00am' is the smallest/earliest time for a given day.

Typically what I do is add one day to the 'end' date range ('8/19/00' becomes
'8/19/00 12:00am') and then search for anything 'less than' this new date, eg,

where fut_date < '8/19/00'

In the case where you're given 8/18/00 to work with ... look at using the
dateadd() function to add the extra day.
--
Mark A. Parsons

Iron Horse, Inc. iron_horse@compuserve.com
Pegasys (200), Ltd pegasys@internet.co.nz


Anonymous Posted on 2000-08-23 15:27:06.0Z
From: Anonymous@sybase.com
Date: Wed, 23 Aug 2000 11:27:06 -0400
Newsgroups: sybase.public.sqlserver.nt
Subject: Re: Question about date ranges in SQL
Message-ID: <33E4A9584C0F5C6C0054E12785256944.0082FBEC85256943@webforums>
References: <2AECD03E73C94811005513A985256943.005513B785256943@webforums> <39A30B7E.5F5463C6@_internet.co.nz>
Lines: 9
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.sqlserver.nt:1707
Article PK: 1089177

Thanks Mark! Today, we insert the data into the column in question
using getdate() so the time is included. However, the 1999 data, which
was converted over from the legacy app, was inserted without
the time, so the server defaulted it to 12:00AM as you pointed out.
That's why the range worked for the 1999 data but not the 2000.

Thanks again for the help!

Jim