I'll provide an update in a little bit, but I think I might have solved it by writing to the date field using JDBC_SETSTRING instead of JDBC_SETDATE
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, April 07, 2015 1:47 PM
To: Midrange Systems Technical Discussion
Subject: RE: issue with dates in sql server
Do you have some samples of the dates? For example, on row 1 were date1 through date5 all originally (input - SMODT) the same date? Or were they significantly different?
There are some strange quirks in MS in converting dates. For example the year 1900 was not a leap year (only '00 years that are divisible by 400, like 2000, are). Yet if you set an Excel cell to = date(00,02,25) and set the cell below it to
=A1+1
and copy it down so you have
=A2+1
=A3+1
and so on you will see 2/29/1900 which is an erroneous date. It's a bug they carried over from Lotus 123 so they could be migration compatible.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "Smith, Mike" <Mike_Smith@xxxxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 04/07/2015 01:01 PM
Subject: RE: issue with dates in sql server
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
John,
We are writing to a date/time field in sql server and we had the same
thought about time zone, but what doesn't make sense to me, is that it is
random and dates on the same record aren't consistently changing by a day.
For Example
record 10 date1(matches),---- date2( off by a day),----
date3(matches),---- date4( matches),----date5(matches)
Record 11 date1( matches),---- date2( matches),----
date3(matches),---- date4(matches),--- date5(matches)
Record 12 date1(matches), -----date2(matches),------ date3(off by
day),----date4(matches), ---date5(matches)
So, if it is a time zone issue, why wouldn't it happen across the board.
test(de) *ISO SMODT;
if not %error ;
SODate = %date(SMODT : *ISO);
Else ;
SODate = DefaultDate;
EndIf;
If SODate = DefaultDate ;
JDBC_setDate (stmt: 17: SODate: *ON);
Else ;
JDBC_setDate (stmt: 17: SODate);
EndIf ;
Since I started this email, I changed the fields in the SQL Server table
to Date instead of Date/Time.
The same thing is occurring.
Ps. I'm running this middle of the day Easter time zone.
Mike
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
John Yeung
Sent: Monday, April 06, 2015 5:07 PM
To: Midrange Systems Technical Discussion
Subject: Re: issue with dates in sql server
On Mon, Apr 6, 2015 at 3:51 PM, Smith, Mike <Mike_Smith@xxxxxxxxxxxxxxxx>
wrote:
On the I, the are stored as 8 digit numeric. I'm converting them to
date fields prior to writing them to Sql Server.
It may be relevant to say exactly how you're doing this.
Randomly(or so it seems) dates are a day off. For example date on the
I is 1994/12/05 and 1994/12/04 on the Sql Server.
I've run it through debug and the date is correct prior to the write
operation.
It doesn't happen on every record or on every date field.
Anyone have any thoughts on why this might be happening?
Without hearing more, my completely wild shot in the dark is that at some
point, you are converting your data to a field/variable which is some kind
of datetime, rather than just a date. Then things like time zone can come
into play.
John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
NOTICE: This message, including any attachment, is intended as a
confidential and privileged communication. If you have received this
message in error, or are not the named recipient(s), please immediately
notify the sender and delete this message.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.
midrange.com
