SELECT DATEADD(hh, 23, DATEADD(mi,59 , DATEADD(ss,59 , '2019-09-22 00:00:00.000'))) SELECT DATEADD(dd,1, DATEADD(ss, -1 , '2019-09-22 00:00:00.000'))
result
2019-09-22 23:59:59.000 2019-09-22 23:59:59.000
both have same result but witch is better as performance
Advertisement
Answer
Neither is “better”. There can be multiple ways to express something in SQL. For instance, here are some other ways:
select convert(datetime, '2020-09-22') + convert(datetime, '23:59:59') select datetimefromparts(2020, 09, 22, 23, 59, 59, 0) select convert(datetime, '2020-09-22' + ' 23:59:59')
For your particular examples, the first has three function calls and the second has two. That would normally mean that the second is a wee, wee bit faster. However, the compiler probably pre-calculates these anyway, so that doesn’t make a difference.
More important is why you would want to do this. I have see this type of calculation used to support between with datetime values:
where getdate() between startdt and enddt
If that is your intention, fix your logic. It is much better to have the upper bound be non-inclusive, so you write:
where getdate() >= startdt and getdate < enddt
Then the you can use ‘2019-06-23’ for the end date, not worry about “getting the last second”, and not having a bug where you miss the last second of every day.
Aaron Bertrand has an extensive discussion on the use of between with date/time values: What do BETWEEN and the devil have in common?