I think that this is related to parameter sniffing and the need to parameterize your input params to local params within the SP. Adding with recompile causes the execution plan to be recreated and eliminates much of the benefits of having a SP. We were using With Recompile on many reports in an attempt to … Read more
CONVERT(datetime, ‘24.04.2012’, 104) Should do the trick. See here for more info: CAST and CONVERT (Transact-SQL)
If you are using SQL Server 2012 and above, you can use FORMATMESSAGE. eg. DECLARE @s NVARCHAR(50) = ‘World’; DECLARE @d INT = 123; SELECT FORMATMESSAGE(‘Hello %s, %d’, @s, @d) — RETURNS ‘Hello World, 123’ More examples from MSDN: FORMATMESSAGE SELECT FORMATMESSAGE(‘Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d’, 5, -5, 50, -50, … Read more
The posted answers work but are clumsy and slow. You can do what I call parallel aggregation: SELECT ID, SUM(case when question_id = 1 then 1 else 0 end) as sum1, SUM(case when question_id = 2 then 1 else 0 end) as sum2, SUM(case when question_id = 3 then 1 else 0 end) as sum3 … Read more
A date is a point in time, not a time span. ’12/31/2010′ is a point, too. Namely, it’s the midnight of the 31st of December. Everything that happened after this point is ignored. That’s exactly the behaviour you want (even if you haven’t realised that yet). Do not think that when you choose to omit … Read more
Easy, first add whole days, then add the remaining ms. There are 86,400,000 milliseconds in a day. declare @unixTS bigint set @unixTS = 1359016610667 select dateadd(ms, @unixTS%(3600*24*1000), dateadd(day, @unixTS/(3600*24*1000), ‘1970-01-01 00:00:00.0’) ) The result is 2013-01-24 08:36:50.667
This will truncate the milliseconds. declare @X datetime set @X = ‘2012-01-25 17:24:05.784’ select convert(datetime, convert(char(19), @X, 126)) or select dateadd(millisecond, -datepart(millisecond, @X), @X) CAST and CONVERT DATEADD DATEPART
A cursor will only accept a select statement, so if the SQL really needs to be dynamic make the declare cursor part of the statement you are executing. For the below to work your server will have to be using global cursors. Declare @UserID varchar(100) declare @sqlstatement nvarchar(4000) –move declare cursor into sql to be … Read more
ISNUMERIC will not do – it tells you that the string can be converted to any of the numeric types, which is almost always a pointless piece of information to know. For example, all of the following are numeric, according to ISNUMERIC: £, $, 0d0 If you want to check for digits and only digits, … Read more
That method doesn’t work in SQL Server. You can accomplish the same thing in a couple different ways: 1.) Use the code for each aliased column instead of the alias: (SELECT COUNT(*) FROM UserEvent UE WHERE UE.EventTypeID = 1 AND UE.PracticeID = au.PracticeID AND (UE.EventDate BETWEEN @Date1 and @Date2) – COUNT(CASE WHEN udi.DevicePlatform = ‘iOS’ … Read more