{"id":1763,"date":"2021-12-05T14:39:36","date_gmt":"2021-12-05T05:39:36","guid":{"rendered":"https:\/\/www.kwonline.org\/memo2\/?p=1763"},"modified":"2022-01-18T14:18:08","modified_gmt":"2022-01-18T05:18:08","slug":"tsql-to-convert-unixtime-to-local-datetime","status":"publish","type":"post","link":"https:\/\/www.kwonline.org\/memo2\/2021\/12\/05\/tsql-to-convert-unixtime-to-local-datetime\/","title":{"rendered":"T-SQL \u3067 unix timestamp \u3092 datetime\u306b\u5909\u63db"},"content":{"rendered":"<p>&nbsp;<br \/>\nSQL Server 2019 \u306e\u30c6\u30fc\u30d6\u30eb\u306b\u683c\u7d0d\u3055\u308c\u3066\u308b UNIX TIMESTAMP \u3092\u65e5\u672c\u6642\u9593\u306e\u65e5\u4ed8\u306b\u5909\u63db\u3057\u305f\u304b\u3063\u305f\u306e\u3067\u30e1\u30e2\u3002<br \/>\nDATEADD \u3092\u4f7f\u3063\u30669\u6642\u9593\u5206\u8db3\u3057\u305f\u4e0a\u3067\u30bf\u30a4\u30e0\u30be\u30fc\u30f3\u3092 &#8216;Tokyo Standard Time&#8217; \u306b\u3059\u308b\u3002 <\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDECLARE @UnixTimestamp INT, @LocalTimeOffset  INT;\r\n\r\nSET @UnixTimestamp = 1638680400;\r\nSET @LocalTimeOffset = DATEDIFF(second,GETUTCDATE(),GETDATE());\r\n\r\nSELECT dateadd(second, (@UnixTimestamp + @LocalTimeOffset), '1970-01-01') at time zone 'Tokyo Standard Time';\r\n<\/pre>\n<p>Japan Standard Time \u3058\u3083\u306a\u304f\u3066 Tokyo Standard Time \u306a\u306e\u306d\u3002<br \/>\n<div id=\"attachment_1768\" style=\"width: 976px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-1768\" src=\"https:\/\/www.kwonline.org\/memo2\/wp-content\/uploads\/2021\/12\/tsql-2021-12-05.png\" alt=\"\" width=\"966\" height=\"286\" class=\"size-full wp-image-1768\" srcset=\"https:\/\/www.kwonline.org\/memo2\/wp-content\/uploads\/2021\/12\/tsql-2021-12-05.png 966w, https:\/\/www.kwonline.org\/memo2\/wp-content\/uploads\/2021\/12\/tsql-2021-12-05-300x89.png 300w, https:\/\/www.kwonline.org\/memo2\/wp-content\/uploads\/2021\/12\/tsql-2021-12-05-768x227.png 768w, https:\/\/www.kwonline.org\/memo2\/wp-content\/uploads\/2021\/12\/tsql-2021-12-05-624x185.png 624w\" sizes=\"auto, (max-width: 966px) 100vw, 966px\" \/><p id=\"caption-attachment-1768\" class=\"wp-caption-text\">T-SQL \u3067 UNIX TIMESTAMP \u3092 DATETIME \u306b\u5909\u63db<\/p><\/div><\/p>\n<p>\u3055\u3089\u306b\u3001\u3053\u3053\u3067\u5f97\u305f DATETIME \u3092 DATE \u306b\u5909\u63db\u3059\u308b\u306a\u3089 CONVERT \u3092\u4f7f\u3048\u3070OK.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSELECT convert(date, date_time);\r\n<\/pre>\n<div id=\"attachment_1793\" style=\"width: 196px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-1793\" src=\"https:\/\/www.kwonline.org\/memo2\/wp-content\/uploads\/2021\/12\/tsql-2022-01-18.png\" alt=\"\" width=\"186\" height=\"116\" class=\"size-full wp-image-1793\" \/><p id=\"caption-attachment-1793\" class=\"wp-caption-text\">DATETIME \u3092 DATE \u306b\u5909\u63db<\/p><\/div>\n<p>&nbsp;<\/p>\n<h3>\u304a\u307e\u3051<\/h3>\n<p>\u3064\u3044\u3067\u306b T-SQL \u3067 1 \u65e5\u524d\u306e\u65e5\u4ed8 datetime \u3092\u53d6\u5f97\u3059\u308b\u306a\u3089 DATEADD \u3067 -1 \u3092\u8db3\u305b\u3070\u3044\u3044\u3002<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nselect dateadd(day, -1, getdate());\r\n<\/pre>\n<div id=\"attachment_1769\" style=\"width: 401px\" class=\"wp-caption alignnone\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-1769\" src=\"https:\/\/www.kwonline.org\/memo2\/wp-content\/uploads\/2021\/12\/tsql-2021-12-05-2.png\" alt=\"\" width=\"391\" height=\"227\" class=\"size-full wp-image-1769\" srcset=\"https:\/\/www.kwonline.org\/memo2\/wp-content\/uploads\/2021\/12\/tsql-2021-12-05-2.png 391w, https:\/\/www.kwonline.org\/memo2\/wp-content\/uploads\/2021\/12\/tsql-2021-12-05-2-300x174.png 300w\" sizes=\"auto, (max-width: 391px) 100vw, 391px\" \/><p id=\"caption-attachment-1769\" class=\"wp-caption-text\">DATEADD \u3067 1 \u65e5\u524d\u306e\u65e5\u4ed8\u3092\u53d6\u5f97<\/p><\/div>\n<p>T-SQL \u6163\u308c\u308b\u3068\u4fbf\u5229\u3002<br \/>\n&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; SQL Server 2019 \u306e\u30c6\u30fc\u30d6\u30eb\u306b\u683c\u7d0d\u3055\u308c\u3066\u308b UNIX TIMESTAMP \u3092\u65e5\u672c\u6642\u9593\u306e\u65e5\u4ed8\u306b\u5909\u63db\u3057\u305f\u304b\u3063\u305f\u306e\u3067\u30e1\u30e2\u3002 DATEADD \u3092\u4f7f\u3063\u30669\u6642\u9593\u5206\u8db3\u3057\u305f\u4e0a\u3067\u30bf\u30a4\u30e0\u30be\u30fc\u30f3\u3092 &#8216;T [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,20,24,23],"tags":[],"class_list":["post-1763","post","type-post","status-publish","format-standard","hentry","category-azure","category-sql","category-sql-server","category-t-sql"],"_links":{"self":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts\/1763","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/comments?post=1763"}],"version-history":[{"count":6,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts\/1763\/revisions"}],"predecessor-version":[{"id":1794,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/posts\/1763\/revisions\/1794"}],"wp:attachment":[{"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/media?parent=1763"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/categories?post=1763"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kwonline.org\/memo2\/wp-json\/wp\/v2\/tags?post=1763"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}