My seniors suggested me not to use the Between clause for Datetime. But whenever i tried to found the reason not to use Between clause nobody gave satisfactory answer. One day i caught in a problem where Between clause was not working. \ud83d\ude42 I was very happy. you must be thinking that why the man is happy even if his code fails ? This time i got the answer of my question.<\/p>\n
Why not to use the Between clause in Date comparison?<\/strong><\/p>\n I will explain this problem with below example.<\/p>\n <\/p>\n Lets create a simple table (Table data type) in \u00a0SQL server in which one column is Id column and another is Datetime column.<\/p>\n Insert few Dummy Data in Table<\/p>\n Lets run below Query:<\/p>\n And you will get unexpected output here:<\/p>\n To resolve above problem and if you think only in Between Clause <\/strong>way , you might think to reduce last day by 1 like below query:<\/p>\n This means Day between 23-Sep to 23-Sep, which will again not work. It will only display the date which have time as 12:00 AM on 23 Sep. And the output of above Query: SQL Server Date Comparison Tips : Never user Between clause with DateTime in SQL Server<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[14],"tags":[292],"class_list":["post-1147","post","type-post","status-publish","format-standard","hentry","category-sqlserver","tag-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1006,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/case-statement-in-where-clause-sql-server-conditional-where-clause\/","url_meta":{"origin":1147,"position":0},"title":"Case statement in Where Clause – SQL Server – Conditional Where clause","author":"Jitendra","date":"September 9, 2010","format":false,"excerpt":"Using Case statement in Where Clause in SQl Server. This script is used to create the conditional where clause","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"Case statement in Where Clause","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/Case-statement-in-Where-Clause.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1161,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-pivot-table-example\/","url_meta":{"origin":1147,"position":1},"title":"SQL Server Pivot Table Example","author":"Jitendra","date":"September 29, 2010","format":false,"excerpt":"Example and tutorial of using PIVOT in SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server Pivot Table","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/SQL-Server-Pivot-Table.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2861,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/pagination-and-switch-case-in-where-clause-sql-server\/","url_meta":{"origin":1147,"position":2},"title":"Pagination and Switch Case in Where clause – SQL Server","author":"Jitendra","date":"May 19, 2012","format":false,"excerpt":"Example of using pagination and switch case in where clause in SQL server","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"SQL Server Employee Table","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2012\/05\/SQL-Server-Employee-Table.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":829,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/convert-rows-into-comma-separated-values-column-sql-server\/","url_meta":{"origin":1147,"position":3},"title":"Convert rows into comma separated values column – SQL Server","author":"Jitendra","date":"October 4, 2010","format":false,"excerpt":"Convert rows into comma separated values column - SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":492,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/get-first-day-and-last-day-of-month-sql-server\/","url_meta":{"origin":1147,"position":4},"title":"Get First Day and Last Day of Month \u2013 SQL Server","author":"Jitendra","date":"July 2, 2010","format":false,"excerpt":"Get the first day and last day of the month in SQL Server using Scalar functions","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":498,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/get-first-day-and-last-day-of-week-sql-server\/","url_meta":{"origin":1147,"position":5},"title":"Get First Day and Last Day of Week – SQL Server","author":"Jitendra","date":"July 2, 2010","format":false,"excerpt":"Scalar function to get the first Day and Last day of the Week in SQL Server","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/1147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=1147"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/1147\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=1147"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=1147"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=1147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\nDECLARE @Temp Table (id INT, val DATETIME)\n<\/pre>\n
\nINSERT INTO @Temp VALUES (1,'2010-09-23 00:00:00.000') --Time 12:00 AM\nINSERT INTO @Temp VALUES (2,'2010-09-24 00:00:00.000') --Time 12:00 AM\nINSERT INTO @Temp VALUES (3,'2010-09-24 01:00:00.000') --Time NOT 12:00 AM\nINSERT INTO @Temp VALUES (3,'2010-09-23 11:15:00.000') --Time NOT 12:00 AM\n<\/pre>\n
\nSelect * from @Temp\nWHERE val BETWEEN '23-Sep-2010' AND '24-Sep-2010' --Bug if Time 12:00 AM , it will be included\n<\/pre>\n
\nid val
\n----------- -----------------------
\n1 2010-09-23 00:00:00.000
\n2 2010-09-24 00:00:00.000
\n3 2010-09-23 11:15:00.000
\n<\/code>
\nAs you can see, there is an error in Output. with 23-sep, 24-Sep Date is also coming.<\/p>\n\nSelect * from @Temp\nWHERE val BETWEEN '23-Sep-2010' AND dateadd(d,-1,'24-Sep-2010')\n<\/pre>\n
\nSo, the best solution of above problem is to use the < and > operators like below Query:<\/p>\n\nSelect * from @Temp\nWHERE val >= '23-Sep-2010' AND val < '24-Sep-2010'\n<\/pre>\n
\n
\nid val
\n----------- -----------------------
\n1 2010-09-23 00:00:00.000
\n3 2010-09-23 11:15:00.000
\n<\/code>
\nThe above output looks correct and will work in any scenario. \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"