The thing about collations is that although the database has its own collation, every table, and every column can have its own collation. If not specified it takes the default of its parent object, but can be different. When you change collation of the database, it will be the new default for all new tables … Read more
You can convert it to datetime, and then use DATEADD(DAY, -30, date). See here. edit I suspect many people are finding this question because they want to substract from current date (as is the title of the question, but not what OP intended). The comment of munyul below answers that question more specifically. Since comments … Read more
You do not need to use ORDER BY in inner query after WHERE clause because you have already used it in ROW_NUMBER() OVER (ORDER BY VRDATE DESC). SELECT * FROM ( SELECT Stockmain.VRNOA, item.description as item_description, party.name as party_name, stockmain.vrdate, stockdetail.qty, stockdetail.rate, stockdetail.amount, ROW_NUMBER() OVER (ORDER BY VRDATE DESC) AS RowNum –< ORDER BY FROM … Read more
I had this problem and it turned out I was trying to restore to the wrong version of SQL. If you want more information on what’s going on, try restoring the database using the following SQL: RESTORE DATABASE <YourDatabase> FROM DISK='<the path to your backup file>\<YourDatabase>.bak’ That should give you the error message that you … Read more
While M.Ali’s answer will get you the result, since you are using SQL Server 2012 I would unpivot the name and address columns slightly different to get the final result. Since you are using SQL Server 2012, you can use CROSS APPLY with VALUES to unpivot these multiple columns into multiple rows. But before you … Read more
You’re right that it’s tricky to find anything in the documentation. But searching the Connect website, I managed to find this gem: Today, you can use CLR aggregates with OVER clause and PARTITION BY just like regular aggregate functions. Once we have support for window functions… Which was a response from Microsoft. However, searching on … Read more
SQL-Server has no TO_DATE function. You have to use convert. See here — Specify a datetime string and its exact format SELECT TO_DATE(‘2012-06-05’, ‘YYYY-MM-DD’) FROM dual; — Specify a datetime string and style 102 (ANSI format), raises an error if conversion fails SELECT CONVERT(DATETIME, ‘2012-06-05’, 102); — TRY_CONVERT available since SQL Server 2012 (returns NULL … Read more
By default, everything will run in 64 bit on the servers. To change this behaviour, you need to indicate that the 32bit version of dtexec should be used. For the 2012 SSISDB, we have two easy ways of invoking our packages: SQL Agent and the catalog.start_execution method. catalog.start_execution For single serving package runs, you can … Read more
I thought about using recursive CTE, but, as far as I know, it’s not possible in SQL Server to use UNION to connect anchor member and a recursive member of recursive CTE (I think it’s possible to do in PostgreSQL), so it’s not possible to eliminate duplicates. declare @i int with cte as ( select … Read more
You want to find consecutive groups of similar rows and then aggregation. I like the difference of row numbers approach: select name, act, min(startt) as startt, max(endd) as endd from (select i.*, row_number() over (partition by name, act order by rn) as seqnum_na, row_number() over (partition by name order by rn) as seqnum_n from input … Read more