Menu
I have been having an issue with using the following:
Column_Name BETWEEN @StartDate AND @EndDate. This is because the @EndDate = 00:00:00.000 for the time, which doesn't pick up all the values for that day.
How would I convert the @EndDate (Always 00:00:00.000) to always be Date + 23:59:59.999?
529
If you are using mySql or similar SQL engines then you can use the DATEADD method to add hour, date, month, year to a date. select dateadd(hour, 5, now()); If you are using postgreSQL you can use the interval option to add values to the date.
The answer is SELECT DATEADD(ms, -3, '2008-01-24') , the explanation is below. Remember, always make sure that you do math against input parameters, NOT columns, or you will kill the SARG-ability of the query, which means indexes that might have been used aren't. This is great!
One option that avoids needing to add EndDate + 23:59:59.999 is to not use the between comparison and instead use column_name >= @StartDate and column_name < @EndDate +1
137
Please note the accuracy and rounding of the DATETIME type in SQL Server 2005:
datetime values are rounded to increments of .000, .003, or .007 seconds
SQL Server 2008 introduced the DATETIME2 type which has an accuracy of 100 nanoseconds. So in SQL Server 2008 you could do:
DECLARE @d DATETIME = '2011-10-07 00:00:00.000' SELECT DATEADD(MS, -1, DATEADD(D, 1, CONVERT(DATETIME2, @d))) Alternatively you may want to avoid the BETWEEN operator in this case:
@StartDate <= Column_Name AND Column_Name < DATEADD(D, 1, @EndDate) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
