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.

date args not accurate with MS SQL....OK with SQL Anywhere

4 posts in DataWindow Last posting was on 2008-07-25 13:44:00.0Z
GW Posted on 2008-07-17 01:23:41.0Z
From: "GW" <gw@gw>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: date args not accurate with MS SQL....OK with SQL Anywhere
Lines: 21
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <487e9f1d$1@forums-1-dub>
Date: 16 Jul 2008 18:23:41 -0700
X-Trace: forums-1-dub 1216257821 10.22.241.152 (16 Jul 2008 18:23:41 -0700)
X-Original-Trace: 16 Jul 2008 18:23:41 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87401
Article PK: 416657

Hi folks

I have some proven dw's that retrieve rows based on dates. I have been using
the between operator fine with SQL Anywhere but with MS SQL it's not
retrieving precisely what I request. For example if my args are today's date
for both then anything with today's date will not retrieve. But, if I
advance the second arg to tomorrow all will retrieve. I've verified the
correct args are being passed and I have also dropped the "between" to arg1
>= today and <= to arg2 but the behaviour is the same.
I suspect it has something to do with it being a datetime field so I'm not
sure how to set the syntax to get the precision I need.

here is the syntax I'm using:

WHERE (misc_log_entries.date_1 >= :date1 AND
misc_log_entries.date_1 <= :date2) AND
misc_log_entries.map = :map

Thanks


M. Searer Posted on 2008-07-17 01:50:01.0Z
From: "M. Searer" <nospam@nospam.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <487e9f1d$1@forums-1-dub>
In-Reply-To: <487e9f1d$1@forums-1-dub>
Subject: Re: date args not accurate with MS SQL....OK with SQL Anywhere
Lines: 27
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6001.18000
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18000
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <487ea549@forums-1-dub>
Date: 16 Jul 2008 18:50:01 -0700
X-Trace: forums-1-dub 1216259401 10.22.241.152 (16 Jul 2008 18:50:01 -0700)
X-Original-Trace: 16 Jul 2008 18:50:01 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87403
Article PK: 416659

MS SQL has a DATETIME, not a date. if you send up a date without the time portion, then it assumes 12:00 midnight.
If your dates in ms sql have a time component then they will not match anything.
so, you need to look at your data

"GW" <gw@gw> wrote in message news:487e9f1d$1@forums-1-dub...
> Hi folks
>
> I have some proven dw's that retrieve rows based on dates. I have been using
> the between operator fine with SQL Anywhere but with MS SQL it's not
> retrieving precisely what I request. For example if my args are today's date
> for both then anything with today's date will not retrieve. But, if I
> advance the second arg to tomorrow all will retrieve. I've verified the
> correct args are being passed and I have also dropped the "between" to arg1
> >= today and <= to arg2 but the behaviour is the same.
> I suspect it has something to do with it being a datetime field so I'm not
> sure how to set the syntax to get the precision I need.
>
> here is the syntax I'm using:
>
> WHERE (misc_log_entries.date_1 >= :date1 AND
> misc_log_entries.date_1 <= :date2) AND
> misc_log_entries.map = :map
>
> Thanks
>
>


GW Posted on 2008-07-17 02:31:26.0Z
From: "GW" <gw@gw>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <487e9f1d$1@forums-1-dub> <487ea549@forums-1-dub>
Subject: Re: date args not accurate with MS SQL....OK with SQL Anywhere
Lines: 36
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <487eaefe@forums-1-dub>
Date: 16 Jul 2008 19:31:26 -0700
X-Trace: forums-1-dub 1216261886 10.22.241.152 (16 Jul 2008 19:31:26 -0700)
X-Original-Trace: 16 Jul 2008 19:31:26 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87405
Article PK: 416661

OK, I see what you getting at. I changed the arg types and all seems fine

thanks

"M. Searer" <nospam@nospam.com> wrote in message
news:487ea549@forums-1-dub...
> MS SQL has a DATETIME, not a date. if you send up a date without the time
> portion, then it assumes 12:00 midnight. If your dates in ms sql have a
> time component then they will not match anything.
> so, you need to look at your data
>
>
> "GW" <gw@gw> wrote in message news:487e9f1d$1@forums-1-dub...
>> Hi folks
>>
>> I have some proven dw's that retrieve rows based on dates. I have been
>> using the between operator fine with SQL Anywhere but with MS SQL it's
>> not retrieving precisely what I request. For example if my args are
>> today's date for both then anything with today's date will not retrieve.
>> But, if I advance the second arg to tomorrow all will retrieve. I've
>> verified the correct args are being passed and I have also dropped the
>> "between" to arg1
>> >= today and <= to arg2 but the behaviour is the same.
>> I suspect it has something to do with it being a datetime field so I'm
>> not sure how to set the syntax to get the precision I need.
>>
>> here is the syntax I'm using:
>>
>> WHERE (misc_log_entries.date_1 >= :date1 AND
>> misc_log_entries.date_1 <= :date2) AND
>> misc_log_entries.map = :map
>>
>> Thanks
>


Tyler Cruse Posted on 2008-07-25 13:44:00.0Z
Reply-To: "Tyler Cruse" <tcruse@trlx.com>
From: "Tyler Cruse" <tcruse@trlx.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <487e9f1d$1@forums-1-dub> <487ea549@forums-1-dub>
In-Reply-To: <487ea549@forums-1-dub>
Subject: Re: date args not accurate with MS SQL....OK with SQL Anywhere
Lines: 32
Organization: Trellix Engineering
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6001.18000
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18000
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4889d8a0$1@forums-1-dub>
Date: 25 Jul 2008 06:44:00 -0700
X-Trace: forums-1-dub 1216993440 10.22.241.152 (25 Jul 2008 06:44:00 -0700)
X-Original-Trace: 25 Jul 2008 06:44:00 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87453
Article PK: 416710

FYI - Changes in SQL2008 - Several new date datatypes

"M. Searer" <nospam@nospam.com> wrote in message
news:487ea549@forums-1-dub...
> MS SQL has a DATETIME, not a date. if you send up a date without the time
> portion, then it assumes 12:00 midnight. If your dates in ms sql have a
> time component then they will not match anything.
> so, you need to look at your data
>
>
> "GW" <gw@gw> wrote in message news:487e9f1d$1@forums-1-dub...
>> Hi folks
>>
>> I have some proven dw's that retrieve rows based on dates. I have been
>> using the between operator fine with SQL Anywhere but with MS SQL it's
>> not retrieving precisely what I request. For example if my args are
>> today's date for both then anything with today's date will not retrieve.
>> But, if I advance the second arg to tomorrow all will retrieve. I've
>> verified the correct args are being passed and I have also dropped the
>> "between" to arg1
>> >= today and <= to arg2 but the behaviour is the same.
>> I suspect it has something to do with it being a datetime field so I'm
>> not sure how to set the syntax to get the precision I need.
>>
>> here is the syntax I'm using:
>>
>> WHERE (misc_log_entries.date_1 >= :date1 AND
>> misc_log_entries.date_1 <= :date2) AND
>> misc_log_entries.map = :map
>>
>> Thanks