This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
A sample code from MSDN question |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE [dbo].[productdetails]( | |
[productid] [int] NULL, | |
[Productrstartdate] [date] NULL, | |
[Productenddate] [date] NULL, | |
[EMIInstallment] [int] NULL | |
) | |
GO | |
INSERT [dbo].[productdetails] ([productid], [Productrstartdate], [Productenddate], [EMIInstallment]) | |
VALUES (1, CAST(N'2020-10-02' AS Date), CAST(N'2024-10-02' AS Date), 5) | |
,(2, CAST(N'2020-02-10' AS Date), CAST(N'2021-02-10' AS Date), 2) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE #tmpTable ( | |
Friday varchar(50), | |
Monday varchar(50), | |
Saturday varchar(50), | |
Sunday varchar(50), | |
Thursday varchar(50), | |
Tuesday varchar(50), | |
Wednesday varchar(50), | |
fk_incomes int, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
declare @s varchar(max) = 'Hi my name is M and I have two sister 111) M and 2111) K'---, 3333) W'; | |
select string_agg([value],' ') newString | |
from openjson('["'+replace(@s ,' ', '","') + '"]') | |
where [Value] not like '%[0-9])%' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
declare @s varchar(max) = 'Hi my name is M and I have two sister 1) M and 2) K' | |
;with mycte as ( | |
select Cast(N'<H><r>'+replace(@s ,' ', '</r><r>') + '</r></H>' AS XML) vals | |
) | |
,mycte2 as ( | |
SELECT ROW_NUMBER() OVER (ORDER BY S.a.value('count(.)', 'tinyint')) rn | |
,S.a.value('.', 'VARCHAR(100)') AS splitVal | |
FROM mycte d | |
CROSS APPLY d.[vals].nodes('/H/r') S(a) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
IF OBJECT_ID (N'NthDayOfWeekOfMonth') IS NOT NULL | |
DROP FUNCTION NthDayOfWeekOfMonth | |
GO | |
CREATE FUNCTION NthDayOfWeekOfMonth ( | |
@TheYear CHAR(4), --the year as four characters (e.g. '2014') | |
@TheMonth CHAR(3), --in english (Sorry to our EU collegues) e.g. Jun, Jul, Aug | |
@TheDayOfWeek CHAR(3), -- one of Mon, Tue, Wed, Thu, Fri, Sat, Sun | |
@Nth INT) --1 for the first date, 2 for the second occurence, 3 for the third | |
RETURNS DATETIME |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Create Table test | |
( year int, month int,CustomerID int, AccountNum int, Amount int) | |
GO | |
Insert test | |
values | |
(2019,11,14,1,200), | |
(2019,11,20,1,500), | |
(2019,11,20,2,25), | |
(2019,12,20,1,670), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
https://stackoverflow.com/questions/23166423/how-to-create-view-for-all-tables-in-database | |
DECLARE @SQL nvarchar(MAX) | |
SET @SQL = N'' | |
SELECT | |
@SQL = @SQL + | |
N'IF EXISTS(SELECT 1 FROM sys.objects WHERE name = ''v_' + t.[TABLE_NAME] + ''' AND type = ''V'') BEGIN DROP VIEW [v_' + t.[TABLE_NAME] + '] END | |
EXEC(''CREATE VIEW [v_' + t.[TABLE_NAME] + N'] AS SELECT ' + |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Configure WinSCP with winscp.ini file export from GUI to generate this file from working machine | |
//in c:\Program Files (x86)\WinSCP\ folder | |
//copy this ini file to transfer site settings | |
string logname = "D:\\data\\log\\" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + "_log.xml"; | |
//// Run hidden WinSCP process | |
Process winscp = new Process(); | |
winscp.StartInfo.FileName = "c:\\Program Files (x86)\\WinSCP\\winscp.com"; | |
winscp.StartInfo.Arguments = "/log=\"" + logname + "\""; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE #TEST | |
(OrderITEM NVARCHAR(MAX), CustomerId INT, OrderDate DATETIME) | |
GO | |
INSERT INTO #TEST(OrderITEM, CustomerId,OrderDate) | |
VALUES('A-100 A-105 C-100' ,1,Getdate()-30), | |
('A-111 A-102 D-201' ,2,Getdate()-5), | |
('D-400 G-10 F-220' ,3,Getdate()-20), | |
('H-801 M-451 D-201' ,1,Getdate()-3), | |
('F-701 A-102 D-201' ,4,Getdate()-6), |
OlderNewer