{"id":953,"date":"2012-06-28T09:48:25","date_gmt":"2012-06-28T12:48:25","guid":{"rendered":"http:\/\/www.franklinjr.com\/blog\/?p=953"},"modified":"2012-06-28T09:48:25","modified_gmt":"2012-06-28T12:48:25","slug":"converter-datahora-na-query-do-sql-server","status":"publish","type":"post","link":"https:\/\/www.franklinjr.com\/blog\/index.php\/2012\/06\/28\/converter-datahora-na-query-do-sql-server\/","title":{"rendered":"Converter data\/hora na query do sql server"},"content":{"rendered":"

Alguns exemplos de convers\u00e3o de hora do mssql.<\/p>\n

-- SQL Server string to date \/ datetime conversion - datetime string format sql server\r\n-- MSSQL string to datetime conversion - convert char to date - convert varchar to date\r\n-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)\r\nSELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)\r\nSELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000\r\n\r\n-- Without century (yy) string date conversion - convert string to datetime function\r\nSELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)\r\nSELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000\r\n\r\n-- Convert string to datetime sql - convert string to date sql - sql dates format\r\n-- T-SQL convert string to datetime - SQL Server convert string to date\r\nSELECT convert(datetime, '10\/23\/2016', 101) -- mm\/dd\/yyyy\r\nSELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century\r\nSELECT convert(datetime, '23\/10\/2016', 103) -- dd\/mm\/yyyy\r\nSELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy\r\nSELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy\r\n-- mon types are nondeterministic conversions, dependent on language setting\r\nSELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy\r\nSELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy\r\n-- 2016-10-23 00:00:00.000\r\nSELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss\r\n-- 1900-01-01 20:10:44.000\r\n\r\n-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format\r\nSELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)\r\n-- 2016-10-23 11:02:44.013\r\nSELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy\r\nSELECT convert(datetime, '2016\/10\/23', 111) -- yyyy\/mm\/dd\r\n-- YYYYMMDD ISO date format works at any language setting - international standard\r\nSELECT convert(datetime, '20161023')\r\nSELECT convert(datetime, '20161023', 112) -- ISO yyyymmdd\r\n-- 2016-10-23 00:00:00.000\r\nSELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm\r\n-- 2016-10-23 11:02:07.577\r\nSELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)\r\n-- 1900-01-01 20:10:25.300\r\nSELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)\r\n-- 2016-10-23 20:44:11.000\r\nSELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm\r\n-- 2016-10-23 20:44:11.500\r\n\r\n-- Style 126 is ISO 8601 format: international standard - works with any language setting\r\nSELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)\r\n-- 2008-10-23 18:52:47.513\r\nSELECT convert(datetime, N'23 ???? 1429  6:52:47:513PM', 130) -- Islamic\/Hijri date\r\nSELECT convert(datetime, '23\/10\/1429  6:52:47:513PM',    131) -- Islamic\/Hijri date\r\n\r\n-- Convert DDMMYYYY format to datetime - sql server to date \/ datetime\r\nSELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)\r\n-- 2016-01-31 00:00:00.000\r\n-- SQL Server T-SQL string to datetime conversion without century - some exceptions\r\n-- nondeterministic means language setting dependent such as Mar\/M\u00e4r\/mars\/m\u00e1rc\r\nSELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default\r\nSELECT convert(datetime, '10\/23\/16', 1) -- mm\/dd\/yy U.S.\r\nSELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI\r\nSELECT convert(datetime, '23\/10\/16', 3) -- dd\/mm\/yy UK\/FR\r\nSELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German\r\nSELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian\r\nSELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.\r\nSELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.\r\nSELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss\r\nSELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec\r\nSELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.\r\nSELECT convert(datetime, '16\/10\/23', 11) -- yy\/mm\/dd Japan\r\nSELECT convert(datetime, '161023', 12) -- yymmdd ISO\r\nSELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt\r\nSELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)\r\nSELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.\r\nSELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC<\/pre>\n
Fonte: http:\/\/www.sqlusa.com\/bestpractices\/datetimeconversion\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Alguns exemplos de convers\u00e3o de hora do mssql. — SQL Server string to date \/ datetime conversion – datetime string format sql server — MSSQL string to datetime conversion – convert char to date – convert varchar to date — Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century) SELECT […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[441,51],"tags":[484,475,485,430],"class_list":["post-953","post","type-post","status-publish","format-standard","hentry","category-sql-server-2","category-windows","tag-banco","tag-mssql","tag-sql","tag-sql-server"],"yoast_head":"\r\nConverter data\/hora na query do sql server - Franklin Jr.<\/title>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/www.franklinjr.com\/blog\/index.php\/2012\/06\/28\/converter-datahora-na-query-do-sql-server\/\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.franklinjr.com\\\/blog\\\/index.php\\\/2012\\\/06\\\/28\\\/converter-datahora-na-query-do-sql-server\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"In\u00edcio\",\"item\":\"https:\\\/\\\/www.franklinjr.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Converter data\\\/hora na query do sql server\"}]}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Converter data\/hora na query do sql server - Franklin Jr.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.franklinjr.com\/blog\/index.php\/2012\/06\/28\/converter-datahora-na-query-do-sql-server\/","schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"BreadcrumbList","@id":"https:\/\/www.franklinjr.com\/blog\/index.php\/2012\/06\/28\/converter-datahora-na-query-do-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"In\u00edcio","item":"https:\/\/www.franklinjr.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Converter data\/hora na query do sql server"}]}]}},"_links":{"self":[{"href":"https:\/\/www.franklinjr.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/953","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.franklinjr.com\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.franklinjr.com\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.franklinjr.com\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.franklinjr.com\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=953"}],"version-history":[{"count":4,"href":"https:\/\/www.franklinjr.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/953\/revisions"}],"predecessor-version":[{"id":957,"href":"https:\/\/www.franklinjr.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/953\/revisions\/957"}],"wp:attachment":[{"href":"https:\/\/www.franklinjr.com\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.franklinjr.com\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.franklinjr.com\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}