SELECT statement from MS SQL returns dates with loss of 2-4 days
up vote
1
down vote
favorite
UPD: Solved. I changed DATE fields to DATETIME type in each table. I still have no idea about the "nature" of the problem and why it was so random, but now problem is fixed. Thanks everyone!
I use MS SQL Express 2014 to store some data.
I have 3 different tables, each of them contains a DATE type field.
When I do a select statement to each table via JDBC, the ResultSet in some cases returns dates with loss of 2-4 days.
The code looks like:
ResultSet rs = preparedStatement.executeQuery();
while(rs.next()){
java.sql.Date date = rs.getDate("DATE");
}
Dates in DB looks like: "1942-06-10", "1938-03-15", etc. The loss happens only sometimes, no matter how the date looks. On Java side they become "1942-06-08" or "1938-03-11".
I can't understand the reason of the problem, because
in some cases "date" contains the exact data as field in DB,
and in other cases "date" is losing from 2 to 4 days comparing to DB field.
Thank you!
java
sql-server date datetime asked Nov 9 at 8:32
ReemuS
193
add a comment |
up vote
1
down vote
favorite
UPD: Solved. I changed DATE fields to DATETIME type in each table. I still have no idea about the "nature" of the problem and why it was so random, but now problem is fixed. Thanks everyone!
I use MS SQL Express 2014 to store some data.
I have 3 different tables, each of them contains a DATE type field.
When I do a select statement to each table via JDBC, the ResultSet in some cases returns dates with loss of 2-4 days.
The code looks like:
ResultSet rs = preparedStatement.executeQuery();
while(rs.next()){
java.sql.Date date = rs.getDate("DATE");
}
Dates in DB looks like: "1942-06-10", "1938-03-15", etc. The loss happens only sometimes, no matter how the date looks. On Java side they become "1942-06-08" or "1938-03-11".
I can't understand the reason of the problem, because
in some cases "date" contains the exact data as field in DB,
and in other cases "date" is losing from 2 to 4 days comparing to DB field.
Thank you!
java
sql-server date datetime asked Nov 9 at 8:32
ReemuS
193
3
Could you add example dates from your DB for both cases, along with the result on java side ?
– Arnaud
Nov 9 at 8:35
1
Just check the timezone for the both DB
– SaviNuclear
Nov 9 at 8:35
The dates where the loss happens, are they historic dates, say, more than 100 years ago? Does the loss always happen for dates in that range or only sometimes? Do you get the same loss if you tryLocalDate date = rs.getObject("DATE", LocalDate.class)instead? (Thejava.sql.Dateclass is outdated and has some design issues to it, so all things being equal I would recommend the latter approach.)
– Ole V.V.
Nov 9 at 9:03
1
Dates in DB looks like: "1942-06-10", "1938-03-15", etc. The loss happens only sometimes, no matter how the date looks. On Java side they become "1942-06-08" or "1938-03-11".
– ReemuS
Nov 9 at 9:10
1
Welcome to Stack Overflow. This is vuja de (never seen it before). Please show your query and a few rows of sample data illustrating the problem. You can edit your question to do this.
– O. Jones
Nov 9 at 11:47
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
UPD: Solved. I changed DATE fields to DATETIME type in each table. I still have no idea about the "nature" of the problem and why it was so random, but now problem is fixed. Thanks everyone!
I use MS SQL Express 2014 to store some data.
I have 3 different tables, each of them contains a DATE type field.
When I do a select statement to each table via JDBC, the ResultSet in some cases returns dates with loss of 2-4 days.
The code looks like:
ResultSet rs = preparedStatement.executeQuery();
while(rs.next()){
java.sql.Date date = rs.getDate("DATE");
}
Dates in DB looks like: "1942-06-10", "1938-03-15", etc. The loss happens only sometimes, no matter how the date looks. On Java side they become "1942-06-08" or "1938-03-11".
I can't understand the reason of the problem, because
in some cases "date" contains the exact data as field in DB,
and in other cases "date" is losing from 2 to 4 days comparing to DB field.
Thank you!
java
sql-server date datetime asked Nov 9 at 8:32
ReemuS
193
UPD: Solved. I changed DATE fields to DATETIME type in each table. I still have no idea about the "nature" of the problem and why it was so random, but now problem is fixed. Thanks everyone!
I use MS SQL Express 2014 to store some data.
I have 3 different tables, each of them contains a DATE type field.
When I do a select statement to each table via JDBC, the ResultSet in some cases returns dates with loss of 2-4 days.
The code looks like:
ResultSet rs = preparedStatement.executeQuery();
while(rs.next()){
java.sql.Date date = rs.getDate("DATE");
}
Dates in DB looks like: "1942-06-10", "1938-03-15", etc. The loss happens only sometimes, no matter how the date looks. On Java side they become "1942-06-08" or "1938-03-11".
I can't understand the reason of the problem, because
in some cases "date" contains the exact data as field in DB,
and in other cases "date" is losing from 2 to 4 days comparing to DB field.
Thank you!
java
sql-server date datetime java
sql-server date datetime asked Nov 9 at 8:32
ReemuS
193
asked Nov 9 at 8:32
ReemuS
193
edited Nov 10 at 10:32
asked Nov 9 at 8:32
ReemuS
193
asked Nov 9 at 8:32
ReemuS
193
asked Nov 9 at 8:32
ReemuS
193
193
3
Could you add example dates from your DB for both cases, along with the result on java side ?
– Arnaud
Nov 9 at 8:35
1
Just check the timezone for the both DB
– SaviNuclear
Nov 9 at 8:35
The dates where the loss happens, are they historic dates, say, more than 100 years ago? Does the loss always happen for dates in that range or only sometimes? Do you get the same loss if you tryLocalDate date = rs.getObject("DATE", LocalDate.class)instead? (Thejava.sql.Dateclass is outdated and has some design issues to it, so all things being equal I would recommend the latter approach.)
– Ole V.V.
Nov 9 at 9:03
1
Dates in DB looks like: "1942-06-10", "1938-03-15", etc. The loss happens only sometimes, no matter how the date looks. On Java side they become "1942-06-08" or "1938-03-11".
– ReemuS
Nov 9 at 9:10
1
Welcome to Stack Overflow. This is vuja de (never seen it before). Please show your query and a few rows of sample data illustrating the problem. You can edit your question to do this.
– O. Jones
Nov 9 at 11:47
add a comment |
3
Could you add example dates from your DB for both cases, along with the result on java side ?
– Arnaud
Nov 9 at 8:35
1
Just check the timezone for the both DB
– SaviNuclear
Nov 9 at 8:35
The dates where the loss happens, are they historic dates, say, more than 100 years ago? Does the loss always happen for dates in that range or only sometimes? Do you get the same loss if you tryLocalDate date = rs.getObject("DATE", LocalDate.class)instead? (Thejava.sql.Dateclass is outdated and has some design issues to it, so all things being equal I would recommend the latter approach.)
– Ole V.V.
Nov 9 at 9:03
1
Dates in DB looks like: "1942-06-10", "1938-03-15", etc. The loss happens only sometimes, no matter how the date looks. On Java side they become "1942-06-08" or "1938-03-11".
– ReemuS
Nov 9 at 9:10
1
Welcome to Stack Overflow. This is vuja de (never seen it before). Please show your query and a few rows of sample data illustrating the problem. You can edit your question to do this.
– O. Jones
Nov 9 at 11:47
3
3
Could you add example dates from your DB for both cases, along with the result on java side ?
– Arnaud
Nov 9 at 8:35
Could you add example dates from your DB for both cases, along with the result on java side ?
– Arnaud
Nov 9 at 8:35
1
1
Just check the timezone for the both DB
– SaviNuclear
Nov 9 at 8:35
Just check the timezone for the both DB
– SaviNuclear
Nov 9 at 8:35
The dates where the loss happens, are they historic dates, say, more than 100 years ago? Does the loss always happen for dates in that range or only sometimes? Do you get the same loss if you try
LocalDate date = rs.getObject("DATE", LocalDate.class) instead? (The java.sql.Date class is outdated and has some design issues to it, so all things being equal I would recommend the latter approach.)– Ole V.V.
Nov 9 at 9:03
The dates where the loss happens, are they historic dates, say, more than 100 years ago? Does the loss always happen for dates in that range or only sometimes? Do you get the same loss if you try
LocalDate date = rs.getObject("DATE", LocalDate.class) instead? (The java.sql.Date class is outdated and has some design issues to it, so all things being equal I would recommend the latter approach.)– Ole V.V.
Nov 9 at 9:03
1
1
Dates in DB looks like: "1942-06-10", "1938-03-15", etc. The loss happens only sometimes, no matter how the date looks. On Java side they become "1942-06-08" or "1938-03-11".
– ReemuS
Nov 9 at 9:10
Dates in DB looks like: "1942-06-10", "1938-03-15", etc. The loss happens only sometimes, no matter how the date looks. On Java side they become "1942-06-08" or "1938-03-11".
– ReemuS
Nov 9 at 9:10
1
1
Welcome to Stack Overflow. This is vuja de (never seen it before). Please show your query and a few rows of sample data illustrating the problem. You can edit your question to do this.
– O. Jones
Nov 9 at 11:47
Welcome to Stack Overflow. This is vuja de (never seen it before). Please show your query and a few rows of sample data illustrating the problem. You can edit your question to do this.
– O. Jones
Nov 9 at 11:47
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
Solved. I changed DATE fields to DATETIME type in each table. I still have no idea about the "nature" of the problem and why it was so random, but now problem is fixed. Thanks everyone!
answered Nov 10 at 10:39
ReemuS
193
Very interesting. Thank you.
– Ole V.V.
Nov 10 at 19:16
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
Solved. I changed DATE fields to DATETIME type in each table. I still have no idea about the "nature" of the problem and why it was so random, but now problem is fixed. Thanks everyone!
answered Nov 10 at 10:39
ReemuS
193
Very interesting. Thank you.
– Ole V.V.
Nov 10 at 19:16
add a comment |
up vote
1
down vote
Solved. I changed DATE fields to DATETIME type in each table. I still have no idea about the "nature" of the problem and why it was so random, but now problem is fixed. Thanks everyone!
answered Nov 10 at 10:39
ReemuS
193
Very interesting. Thank you.
– Ole V.V.
Nov 10 at 19:16
add a comment |
up vote
1
down vote
up vote
1
down vote
Solved. I changed DATE fields to DATETIME type in each table. I still have no idea about the "nature" of the problem and why it was so random, but now problem is fixed. Thanks everyone!
answered Nov 10 at 10:39
ReemuS
193
Solved. I changed DATE fields to DATETIME type in each table. I still have no idea about the "nature" of the problem and why it was so random, but now problem is fixed. Thanks everyone!
answered Nov 10 at 10:39
ReemuS
193
answered Nov 10 at 10:39
ReemuS
193
answered Nov 10 at 10:39
ReemuS
193
answered Nov 10 at 10:39
ReemuS
193
193
Very interesting. Thank you.
– Ole V.V.
Nov 10 at 19:16
add a comment |
Very interesting. Thank you.
– Ole V.V.
Nov 10 at 19:16
Very interesting. Thank you.
– Ole V.V.
Nov 10 at 19:16
Very interesting. Thank you.
– Ole V.V.
Nov 10 at 19:16
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53222221%2fselect-statement-from-ms-sql-returns-dates-with-loss-of-2-4-days%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Could you add example dates from your DB for both cases, along with the result on java side ?
– Arnaud
Nov 9 at 8:35
1
Just check the timezone for the both DB
– SaviNuclear
Nov 9 at 8:35
The dates where the loss happens, are they historic dates, say, more than 100 years ago? Does the loss always happen for dates in that range or only sometimes? Do you get the same loss if you try
LocalDate date = rs.getObject("DATE", LocalDate.class)instead? (Thejava.sql.Dateclass is outdated and has some design issues to it, so all things being equal I would recommend the latter approach.)– Ole V.V.
Nov 9 at 9:03
1
Dates in DB looks like: "1942-06-10", "1938-03-15", etc. The loss happens only sometimes, no matter how the date looks. On Java side they become "1942-06-08" or "1938-03-11".
– ReemuS
Nov 9 at 9:10
1
Welcome to Stack Overflow. This is vuja de (never seen it before). Please show your query and a few rows of sample data illustrating the problem. You can edit your question to do this.
– O. Jones
Nov 9 at 11:47