May 20, 2017 at 6:08 am
Hello,
I have this string : 2017-06-08 16:00:00 as a function paramater.
I want to get this string later : '2017-06-08 15:59:00' to use in a between condition.
The aim is to exclude the '16:00' time.
How can I achieve this without a replace because I can have 08,10,12,14,18,20 as hours ?
Regards
May 20, 2017 at 6:18 am
Don't use BETWEEN with Times. use <. >
SELECT *
FROM MyTable
WHERE EventDate < @SomeTime AND EventDate>=DATEADD(minute,-10,@SomeTime)
May 20, 2017 at 7:53 am
First of all, welcome to SSC.
With the function, why are you passing the datetime parameter as a string? If you can, pass it as a datetime instead.
If you want to query on the value using the parameter passed, then pietlinden has posted the best solution I see.
Then you said this:
This got me wondering if you want to query using each of the 6 hours you have listed. This would be different then calling your function 6 times because your function would return 6 rows instead of 1. Using @param to simulate the parameter, your ITVF could then be something like the following:
DECLARE @param Datetime = '06/08/2017';
WITH cte AS (
SELECT d = DATEADD(day, 0, DATEDIFF(day, 0, @param))
)
SELECT dtm = DATEADD(hour, x.h, cte.d)
FROM cte
CROSS APPLY (VALUES(8), (10), (12), (14), (18), (20)) x (h)
ORDER BY dtm;
Then again, I could be way off base here and you really just wanted the < > on the datetime.
May 20, 2017 at 10:19 am
Here's one method:
declare @function_param VARCHAR(100) = '2017-06-08 16:00:00'
declare @result VARCHAR(100)
select @result = CONVERT(varchar(100),DATEADD(minute, -1, @function_param),120)
print @result
May 20, 2017 at 11:17 am
Whatever approach you end up using, make sure you have all possible times covered the way you want them covered.
If, for example, you're getting your string from a datetime value, the data type has 3 decimal placesyou're omitting the millisecond portion. With 1 second resolution, you need to make certain you're handling it the way you want if you have an original value of .500. This is one reason why you'd be better off processing datetime as the original data type instead of handling them as strings.
The >= and < logic also ensures you have the window of time covered accurately. You don't want to end up including (or excluding) something from the calculations when you don't intend to do so. Testing will be your friend here; just make sure you're handling precision and inclusion the way you need to.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply