![]() ![]() That would save storage space on the server, give you greater accuracy at the same precision, be more standards compliant/portable, and allow you to easily adjust the accuracy/precision if your needs change in the future. ![]() In parting you would be able to keep your original query and it would behave as desired if you change your your posted_date column from a DATETIME to a DATETIME2(3). A more qualitative reason to avoid it in SQL Server is it impacts sargability aka you can't perform an index seek and that will frequently result in poorer performance.įor more information on the right way and wrong way to handle date range queries checkout this post by Aaron Bertrand. I don't like option 4 because converting data types to a string for comparison purposes feels dirty to me. 997 fractional seconds is going to be just another magic number that people are going to want to "fix." For some more reasons why BETWEEN isn't widely embraced you might want to checkout this post. I don't like options 2 and 5 because the. Regarding option 3, a very good explanation about some issues can be found here: Cast to date is sargable but is it a good idea? That is especially true if you can change away from using the DATETIME datatype to a DATE data type for your posted_date column. If you are using SQL Server 2008 or newer I think option 3 should be your preferred approach. They convey your intent clearly, and aren't going to break if you update data types. Out of the five options I've presented above I would consider options 1 and 3 the only viable options. WHERE CONVERT(CHAR(8), DateColumn, 112) = '20150727' - Cast to string stripping off time WHERE CAST(posted_date AS DATE) = '' - Use different data type Option 1ĪND posted_date <= ' 23:59:59.997' -Round down and keep equality There are several solutions/workarounds for this that you can use. Notice that the least significant digit can only have one of three potential values: "0", "3", or "7". Further down in the documentation it specifies the rounding rules that SQL Server uses for the least significant digit. ![]() The ultimate guide to the datetime datatypesĪs several others have mentioned in comments and other answers to your question the core issue is 23:59:59.999 is being rounded to 00:00:00.000 by SQL Server. ![]()
0 Comments
Leave a Reply. |