{"id":732,"date":"2005-12-06T11:38:56","date_gmt":"2005-12-06T15:38:56","guid":{"rendered":"http:\/\/wordpress.cephas.net\/?p=732"},"modified":"2005-12-06T11:38:56","modified_gmt":"2005-12-06T15:38:56","slug":"sql-server-group-by-datetime","status":"publish","type":"post","link":"https:\/\/cephas.net\/blog\/2005\/12\/06\/sql-server-group-by-datetime\/","title":{"rendered":"SQL Server Group By Datetime"},"content":{"rendered":"<p>If you&#8217;ve ever used SQL Server, you know that there&#8217;s no such thing as a Date or Time, only Datetime. According to SQL Server Books Online: &#8220;If only a time is specified when setting a datetime or smalldatetime value, the date defaults to January 1, 1900. If only a date is specified, the time defaults to 12:00 A.M. (Midnight).&#8221;, which is all is fine and good but it becomes an issue when you want just the date or just the time from a column that stores both. Say for instance that my boss wants to see the number of orders per day over the last couple years to see which days have the most orders (&#8216;<a href=\"http:\/\/en.wikipedia.org\/wiki\/Cyber_Monday\">cyber monday!<\/a>&#8216;). Unfortunately, there&#8217;s no function called date() or time() which returns just the date portion of the datetime or just the time portion of the datetime:<br \/>\n<code><br \/>\n-- doesn't work...<br \/>\nSELECT date(mydate) as thedate, count(id) as perday<br \/>\nFROM orders<br \/>\nGROUP by thedate<br \/>\nORDER by perday DESC<br \/>\n<\/code><br \/>\nTurns out there&#8217;s a hack that does though:<br \/>\n<code><br \/>\nSELECT distinct convert(varchar,mydate,111) as thedate, count(id) as perday<br \/>\nFROM orders<br \/>\ngroup by convert(varchar,mydate,111)<br \/>\norder by perday DESC<br \/>\n<\/code><br \/>\nHope that makes your day.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>If you&#8217;ve ever used SQL Server, you know that there&#8217;s no such thing as a Date or Time, only Datetime. According to SQL Server Books Online: &#8220;If only a time is specified when setting a datetime or smalldatetime value, the date defaults to January 1, 1900. If only a date is specified, the time defaults &hellip; <a href=\"https:\/\/cephas.net\/blog\/2005\/12\/06\/sql-server-group-by-datetime\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">SQL Server Group By Datetime<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[2],"tags":[],"_links":{"self":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts\/732"}],"collection":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/comments?post=732"}],"version-history":[{"count":0,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/posts\/732\/revisions"}],"wp:attachment":[{"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/media?parent=732"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/categories?post=732"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cephas.net\/blog\/wp-json\/wp\/v2\/tags?post=732"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}