Discrepancies Between SQL Server and Java

Question

I need to replicate a simple procedure from Java code to a SQL Server stored procedure. It will go into SQL Azure db in production, but I am testing it on local SQL Express 12.

Part of this stored procedure is to combine some values ​​into a string.

This is my sample Java code:

import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import com.google.common.base.Strings;

public static String concat() {
  //init variables with sample data
  DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss.SSS");
  Timestamp date = new Timestamp(dateFormat.parse("04/04/2014 21:07:13.897").getTime());

  //format variables into 0-filled strings
  String formattedDate = String.format("%011d", date.getTime() / 1000);

  //concat those strings
  String finalString = ... + formattedDate + ...;
  return finalString;    
}

Variables:

| date                    | formatted_date |
| ----------------------- | -------------- |
| 2014-04-04 21:07:13.897 | 01396638433    |

This is the equivalent in SQL:

DECLARE @date DATETIME;
DECLARE @formatted_date CHAR(11);
DECLARE @final_string CHAR(22);

--init variables with same data as Java code
SET @date = '2014/04/04 21:07:13.897';

--format variables into 0-filled strings
SET @formatted_date = FORMAT(DATEDIFF(s,'1970-01-01 00:00:00', @date), '00000000000');

--concat those strings
SET @final_string = CONCAT(..., @formatted_date, ...);

Variables

| date                    | formatted_date |
| ----------------------- | -------------- |
| 2014-04-04 21:07:13.897 | 01396645633    |

Checking if the output was the same, I noticed that the dates do not match:

Java output:  01396638433
MSSQL output: 01396645633

I opened this site to find out what this difference meant:

Java:  GMT: Fri, 04 Apr 2014 19:07:13 GMT, Your time zone: 4/4/2014 21:07:13 GMT+2
MSSQL: GMT: Fri, 04 Apr 2014 21:07:13 GMT, Your time zone: 4/4/2014 23:07:13 GMT+2

The exact difference is two hours.

I found a query to start with SQL Server to check the time zone settings:

DECLARE @TZ SMALLINT
SELECT @TZ=DATEPART(TZ, SYSDATETIMEOFFSET())

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT

SELECT @TimeZone, CAST(@TZ/60 AS VARCHAR(5))+':'+Cast(ABS(@TZ)%60 AS VARCHAR(5));

Conclusion:

| Time zone               | Offset  |
| ----------------------- | ------- |
| W. Europe Standard Time | 2:0     |

I checked the JVM time zone as follows:

Calendar now = Calendar.getInstance();
System.out.println(now.getTimeZone());
System.out.println(System.getProperties().get("user.timezone").toString());

Conclusion:

sun.util.calendar.ZoneInfo[id="Europe/Berlin",offset=3600000, dstSavings=3600000,
transitions=143, lastRule=java.util.SimpleTimeZone[id=Europe/Berlin, offset=3600000, 
dstSavings=3600000, startYear=0, startMode=2, startMonth=2, startDay=-1,
startDayOfWeek=1, startTime=3600000, startTimeMode=2, endMode=2, endMonth=9,
endDay=-1, endDayOfWeek=1, endTime=3600000, endTimeMode=2]]
Europe/Berlin

How to get equal timestamps between Java and SQL Server?

Answer 1

, , :

TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

SQL

getdate()

getutcdate()

!

Answer 2

SQL Server -

SQL Server getutcdate(), UTC. getdate(), UTC .

select datediff(s, getutcdate(), getdate())

, .

, SQL Server :

DECLARE @date DATETIME;
DECLARE @formatted_date CHAR(11);
DECLARE @final_string CHAR(22);
DECLARE @diff_sec int;

--init variables with same data as Java code
SET @date = '2014/04/04 21:07:13.897';

--get the difference between UTC and local in seconds
SET @diff_sec = datediff(s, getutcdate(), getdate());

--format variables into 0-filled strings
SET @formatted_date = FORMAT(DATEDIFF(s,'1970-01-01 00:00:00', @date) - @diff_sec, '00000000000');

--concat those strings
SET @final_string = CONCAT(..., @formatted_date, ...);

Answer 3

JDBC - - - .

, - \, , 2014-04-04 21:07:13.897 , 2014-04-04 21:07:13.897 CEST ( ), 2014-04-04 21:07:13.897 UTC.

CEST 2 UTC 7200 , , :

1396645633 - 1396645633 = 7200

, Java , , . , 2014-04-04 21:07:13.897 UTC, 2014-04-04 23:07:13.897 CEST SQL Server 2014-04-04 23:07:13.897.

setTimestamp(int parameterIndex, Timestamp x), , setTimestamp(int parameterIndex, Timestamp x, Calendar cal) javadoc:

java.sql.Timestamp, Calendar. Calendar SQL TIMESTAMP, . Calendar . Calendar , , , .

. java.sql.Timestamp?