I am just writing a brief post about a conversation I was asked to join.<\/p>\n\n\n\n
The question paraphrased:<\/p>\n\n\n\n
\nFor dates in an Microsoft SQL Server, when they get passed through Qlik Replicate to Kafka Avro – how are they interpreted? Epoch to utc? Or to the current time zone?<\/p>\n<\/blockquote>\n\n\n\n
I didn’t know the answer so I created the following simple test:<\/p>\n\n\n
\n\nCREATE TABLE dbo.JD_DATES_TEST\n(\n ID INT IDENTITY(1,1) PRIMARY KEY,\n TEST_SMALLDATETIME SMALLDATETIME,\n TEST_DATE date,\n TEST_DATETIME datetime,\n TEST_DATETIME2 datetime2,\n TEST_DATETIMEOFFSET datetimeoffset\n);\n\nGO\n\nINSERT INTO dbo.JD_DATES_TEST VALUES(current_timestamp, current_timestamp, current_timestamp, current_timestamp, current_timestamp);\n\nSELECT * FROM dbo.JD_DATES_TEST;\n\/*\nID TEST_SMALLDATETIME TEST_DATE TEST_DATETIME TEST_DATETIME2 TEST_DATETIMEOFFSET\n----------- ----------------------- ---------- ----------------------- --------------------------- ----------------------------------\n1 2025-06-12 12:04:00 2025-06-12 2025-06-12 12:04:16.650 2025-06-12 12:04:16.6500000 2025-06-12 12:04:16.6500000 +00:00\n\n(1 row(s) affected)\n\n\n*\/\n<\/pre><\/div>\n\n\nOn the other side after passing through Qlik Replicate and then onto Kafka in Avro format; we got:<\/p>\n\n\n
\n{\n "data": {\n "ID": {\n "int": 1\n },\n "TEST_SMALLDATETIME": {\n "long": 1749729840000000\n },\n "TEST_DATE": {\n "int": 20251\n },\n "TEST_DATETIME": {\n "long": 1749729856650000\n },\n "TEST_DATETIME2": {\n "long": 1749729856650000\n },\n "TEST_DATETIMEOFFSET": {\n "string": "2025-06-12 12:04:16.6500000 +00:00"\n },\n "x_y": {\n "string": "1.0.0"\n }\n },\n "beforeData": null,\n "headers": {\n "operation": "REFRESH",\n "changeSequence": "",\n "timestamp": "",\n "streamPosition": "",\n "transactionId": "",\n "changeMask": null,\n "columnMask": null,\n "transactionEventCounter": null,\n "transactionLastEvent": null\n }\n}\n<\/pre><\/div>\n\n\nSo 1749729856650000 equals Thursday, June 12, 2025 12:04:16.650 PM – which is local time.<\/p>\n","protected":false},"excerpt":{"rendered":"
I am just writing a brief post about a conversation I was asked to join. The question paraphrased: For dates in an Microsoft SQL Server, when...<\/p>\n","protected":false},"author":1,"featured_media":1076,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[16,17],"tags":[89,88,36],"class_list":["post-1643","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-qlik-replicate","category-sql","tag-dates","tag-mssql","tag-qlikreplicate"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1643","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1643"}],"version-history":[{"count":3,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1643\/revisions"}],"predecessor-version":[{"id":1646,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1643\/revisions\/1646"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/media\/1076"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1643"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1643"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1643"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}