{"id":7891,"date":"2022-09-10T20:38:29","date_gmt":"2022-09-10T15:08:29","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/"},"modified":"2022-09-10T20:38:29","modified_gmt":"2022-09-10T15:08:29","slug":"solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/","title":{"rendered":"[Solved] I have a query with a between clause that is return wrong days [closed]"},"content":{"rendered":"

[ad_1]
\n<\/p>\n

\n
\n
<\/div>\n
\n
\n

Let’s break this down:<\/p>\n

WHERE convert(varchar(10),F_Presence.ts, 120) \n  between '2022-03-01' and '2022-03-02'\n<\/code><\/pre>\n
    \n
  1. Converting the column to a string means you will never, ever, ever be able to take advantage of an index on that column, whether it exists yet or not.<\/li>\n
  2. Using BETWEEN<\/code> is horrible for date ranges for multiple reasons.<\/li>\n
  3. Using a format like YYYY-MM-DD<\/code> is unsafe, because it can be misinterpreted as YYYY-DD-MM<\/code> in a lot of cases.<\/li>\n<\/ol>\n
    All these and more at Dating Responsibly<\/a>.<\/p>\n
    Here is how the query should work:<\/p>\n
    WHERE F_Presence.ts >= '20220301'\n  AND F_Presence.ts <  '20220303'\n<\/code><\/pre>\n<\/p><\/div>\n
    <\/div>\n<\/div>\n
                2<\/span> <\/p><\/div>\n<\/div>\n
    [ad_2]<\/p>\n
    solved I have a query with a between clause that is return wrong days [closed] <\/p>\n","protected":false},"excerpt":{"rendered":"
    [ad_1] Let’s break this down: WHERE convert(varchar(10),F_Presence.ts, 120) between ‘2022-03-01’ and ‘2022-03-02’ Converting the column to a string means you will never, ever, ever be able to take advantage of an index on that column, whether it exists yet or not. Using BETWEEN is horrible for date ranges for multiple reasons. Using a format like … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[341,500,1872],"class_list":["post-7891","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql","tag-sql-server","tag-stored-procedures"],"yoast_head":"\n[Solved] I have a query with a between clause that is return wrong days [closed] - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] I have a query with a between clause that is return wrong days [closed] - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Let’s break this down: WHERE convert(varchar(10),F_Presence.ts, 120) between '2022-03-01' and '2022-03-02' Converting the column to a string means you will never, ever, ever be able to take advantage of an index on that column, whether it exists yet or not. Using BETWEEN is horrible for date ranges for multiple reasons. Using a format like ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-10T15:08:29+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] I have a query with a between clause that is return wrong days [closed]\",\"datePublished\":\"2022-09-10T15:08:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/\"},\"wordCount\":106,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql\",\"sql-server\",\"stored-procedures\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/\",\"name\":\"[Solved] I have a query with a between clause that is return wrong days [closed] - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-09-10T15:08:29+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] I have a query with a between clause that is return wrong days [closed]\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] I have a query with a between clause that is return wrong days [closed] - JassWeb","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:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] I have a query with a between clause that is return wrong days [closed] - JassWeb","og_description":"[ad_1] Let’s break this down: WHERE convert(varchar(10),F_Presence.ts, 120) between '2022-03-01' and '2022-03-02' Converting the column to a string means you will never, ever, ever be able to take advantage of an index on that column, whether it exists yet or not. Using BETWEEN is horrible for date ranges for multiple reasons. Using a format like ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/","og_site_name":"JassWeb","article_published_time":"2022-09-10T15:08:29+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] I have a query with a between clause that is return wrong days [closed]","datePublished":"2022-09-10T15:08:29+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/"},"wordCount":106,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql","sql-server","stored-procedures"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/","url":"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/","name":"[Solved] I have a query with a between clause that is return wrong days [closed] - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-09-10T15:08:29+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-i-have-a-query-with-a-between-clause-that-is-return-wrong-days-closed\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] I have a query with a between clause that is return wrong days [closed]"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1775798750","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/7891","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=7891"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/7891\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=7891"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=7891"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=7891"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}