The window functions in SQL 2008 are rather limited compared to later versions and if I remember correct you can only partition and you can’t use any rows/range frame limit but I think this might be what you want: ;WITH cte (rn, transactionid, value) AS ( SELECT rn = ROW_NUMBER() OVER (ORDER BY transactionid), transactionid, … Read more
According to this post you’ll have to cast it to a byte array. byte[] dt = dataReader[“dt”] as byte[];
If you make the DELETE dynamic, your stored procedure will successfully ALTER the table, DELETE the records in question, and then ALTER it back. CREATE PROCEDURE [dbo].[OrderHistoryDelete] (@Id UNIQUEIDENTIFIER) AS BEGIN DECLARE @sql VARCHAR(MAX) BEGIN TRANSACTION ALTER TABLE [dbo].[Order] SET ( SYSTEM_VERSIONING = OFF ) SET @sql=”DELETE FROM [dbo].[OrderHistory] WITH (TABLOCKX) WHERE [Id] = “” … Read more
For the field to be able to store unicode characters, you have to use the type nvarchar (or other similar like ntext, nchar). To insert the unicode characters in the database you have to send the text as unicode by using a parameter type like nvarchar / SqlDbType.NVarChar. (For completeness: if you are creating SQL … Read more
I suspect you want to check that it’s after 11pm or before 7am: select * from MyTable where CAST(Created as time) >= ’23:00:00′ or CAST(Created as time) < ’07:00:00′
Try adding this line to the beginning of your stored procedure: SET FMTONLY OFF You can remove this after you have finished importing.
Easy: –start of last week SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0) –end of last week SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) EDIT: The below will handle the Sunday date issue. DECLARE @input varchar(10) –SET @input=”9/9/2012″ — simulates a Sunday SET @input = GETDATE() –start of last week SELECT DATEADD(wk, DATEDIFF(wk, 6, CASE DATEPART(dw,@input) WHEN 1 … Read more
SET is the ANSI standard way of assigning values to variables, and SELECT is not. But you can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. So that in performance is where SELECT will be a winner. … Read more
SET is the ANSI standard way of assigning values to variables, and SELECT is not. But you can use SELECT to assign values to more than one variable at a time. SET allows you to assign data to only one variable at a time. So that in performance is where SELECT will be a winner. … Read more
Sure, just add the same calculation to the GROUP BY clause: select dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))), sum(mwspp.QtyRequired) from manufacturingweekshortagepartpurchasing mwspp where mwspp.buildScheduleSimID = 10109 and mwspp.partID = 8366 group by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))) order by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))) … Read more