title: Timestamp discrepancies between SQL server and Java
body: <p>I need to replicate a simple procedure from Java code to SQL Server stored procedure. It will go into a SQL Azure db in production, but I'm testing it against my local SQL Express 12 install.</p>
  
  <p>A part of this stored procedure is to concatenate some values into a string.</p>
  
  <p>This is my example Java code:</p>
  
  <pre><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;    
  }
  </code></pre>
  
  <p>Variables: </p>
  
  <pre><code>| date                    | formatted_date |
  | ----------------------- | -------------- |
  | 2014-04-04 21:07:13.897 | 01396638433    |
  </code></pre>
  
  <p>This is the equivalent in SQL:</p>
  
  <pre><code>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, ...);
  </code></pre>
  
  <p>Variables:</p>
  
  <pre><code>| date                    | formatted_date |
  | ----------------------- | -------------- |
  | 2014-04-04 21:07:13.897 | 01396645633    |
  </code></pre>
  
  <p>While checking if the output was the same I noticed the dates are not the same:</p>
  
  <pre><code>Java output:  01396638433
  MSSQL output: 01396645633
  </code></pre>
  
  <p>I opened <a href="http://www.epochconverter.com/" rel="nofollow">this site</a> to see what this difference meant:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Exactly two hours difference.</p>
  
  <p>I've found a query to run against SQL Server to check time zone settings:</p>
  
  <pre><code>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));
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>| Time zone               | Offset  |
  | ----------------------- | ------- |
  | W. Europe Standard Time | 2:0     |
  </code></pre>
  
  <p>I checked JVM time zone like this:</p>
  
  <pre><code>Calendar now = Calendar.getInstance();
  System.out.println(now.getTimeZone());
  System.out.println(System.getProperties().get("user.timezone").toString());
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>How can I get equal timestamps between Java and SQL Server?</p>