Re: issue with dates in sql server -- MIDRANGE-L

Hey there Mike,

Several people have complained about this in regards to SQL Server -- but the date logic worked for me on MySQL -- which is really my conundrum, what do you do when something appears to work differently across databases?

If setting it as a string works for you (I expect the SQL Server is automatically casting the string to a date, so that might work perfectly.) Then I'd go ahead and use that.

If Scott M. or John Y. would like to see the code for JDBC_setDate() I can post it here... no biggie:

D setDate...
D PR ExtProc(*JAVA
D :'java.sql.PreparedStatement'
D :'setDate')
D idx like(jInt) value
D date like(jSqlDate) const

*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* JDBC_SetDate(): Set a date value in a prepared stmt
*
* prep = (input) Prepared statement to set value in
* idx = (input) Parameter index to set
* date = (input) value to set
* null = (input/optional) set field to NULL in database
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P JDBC_SetDate...
P B export
D JDBC_SetDate...
D PI 1N
D prep like(PreparedStatement) const
D idx 10I 0 value
D date D datfmt(*iso) const
D null 1N const options(*nopass)
D jdt s like(jSqlDate)
D str s like(jString)
D rc s 1N inz(*ON)
D Date_valueOf PR like(jSqlDate)
D extproc(*java
D : 'java.sql.Date'
D : 'valueOf')
D static
D theDate like(jString)
/free
jdbc_begin_object_group(10);

monitor;
str = s(%char(date:*iso));
jdt = Date_valueOf(str);
if (jdt = *NULL);
rc = *OFF;
else;
if (%parms>=4 and null=*ON);
setDate(prep: idx: *NULL);
else;
setDate(prep: idx: jdt);
endif;
rc = *ON;
endif;
on-error;
rc = *OFF;
endmon;

jdbc_end_object_group();
return rc;
/end-free
P E

On 4/7/2015 1:14 PM, Smith, Mike wrote:

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.