sql-server

[Solved] How to insert a string into an sql server table via a windows form? [closed]

by

AddTof1(string s) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand(“INSERT INTO table1 values(@s)”, connection)) { command.Parameters.AddWithValue(“@s”, s); command.ExecuteNonQuery(); } } } Then you can call this method as; AddTof1(“hello there”); 4 solved How to insert a string into an sql server table via a windows form? [closed]

[Solved] How to convert comma separated value into rows in sql server

by

You have tagged your question with SQL Server 2016, in SQL Server 2016 there is a new function STRING_SPLIT. In SQL Server 2016 your query should be as simple as: declare @tab table ([user_name] varchar(10),Unit varchar(100)) insert into @tab VALUES (‘ABC’,’1,2′) SELECT t.[user_name] , Value as Unit FROM @tab t CROSS APPLY STRING_SPLIT(t.Unit , ‘,’) … Read more

[Solved] Convert text to system date format [duplicate]

by

EDIT From SQL Server 2012 and later you might use the FORMAT function if you want to force a date into a formatted text: https://msdn.microsoft.com/en-us/library/hh213505.aspx With earlier versions you might use something like this: DECLARE @d DATETIME=GETDATE(); DECLARE @TargetFormat VARCHAR(100)=’DD/MM/YYYY’; SELECT CONVERT(VARCHAR(100),@d, CASE @TargetFormat WHEN ‘MM/DD/YYYY’ THEN 101 WHEN ‘DD/MM/YYYY’ THEN 103 –add all formats … Read more

[Solved] Query to return record value in column instead of row?

by

SELECT c.ClientID, c.LastName, c.FirstName, c.MiddleName, CASE WHEN cudf.UserDefinedFieldFormatULink = ’93fb3820-38aa-4655-8aad-a8dce8aede’ THEN cudf.UDF_ReportValue AS ‘DA Status’ WHEN cudf.UserDefinedFieldFormatULink = ‘2144a742-08c5-4c96-b9e4-d6f1f56c76’ THEN cudf.UDF_ReportValue AS ‘FHAP Status’ WHEN cudf.UserDefinedFieldFormatULink = ‘c3d29be9-af58-4241-a02d-9ae9b43ffa’ THEN cudf.UDF_ReportValue AS ‘HCRA Status’ END INTO #Temp FROM Client_Program cp INNER JOIN client c ON c.ulink = cp.clientulink INNER JOIN code_program p ON p.ulink = cp.programulink … Read more

[Solved] How to find all the timestamp values interval by each minute between the two timestamp records

by

Here is one option using an ad-hoc tally/numbers table and a Cross Apply Example Declare @YourTable Table ([Id] int,[Open Date] datetime,[Close Date] datetime) Insert Into @YourTable Values (1,’2019-07-03 16:28:39.497′,’2019-07-04 16:28:39.497′) ,(2,’2019-07-04 15:28:39.497′,’2019-07-05 19:28:39.497′) Select A.* ,TSRange = DateAdd(Minute,N,convert(varchar(16),[Open Date],20)) From @YourTable A Cross Apply ( Select Top (DateDiff(MINUTE,[Open Date],[Close Date])-1) N=Row_Number() Over (Order By (Select … Read more

[Solved] How to find all the timestamp values interval by each minute between the two timestamp records

by

Introduction Finding the timestamp values between two timestamp records can be a difficult task. However, with the right approach, it can be done quickly and easily. In this article, we will discuss how to find all the timestamp values interval by each minute between two timestamp records. We will discuss the different methods that can … Read more

[Solved] Dynamic convert Row to Column using group by

by

try this, create table #tmp (MR_ID varchar(50),DR_ID int) insert into #tmp VALUES (‘MR_123’, 1),(‘MR_123’, 3),(‘MR_124’, 4),(‘MR_124’, 5) ,(‘MR_124’, 6),(‘MR_125′, 0) declare @DRCol varchar(50) declare @Prefix varchar(20)=’DR_’ ;With CTE as ( select * ,ROW_NUMBER()over(partition by MR_ID order by DR_ID)rn from #tmp ) select top 1 @DRCol=stuff((select ‘,’+'[‘+@Prefix+cast(rn as varchar)+’]’ from cte c1 where c.mr_id=c1.mr_id for xml … Read more

[Solved] get statistics information by SQL query efficiently for table with 3 columns and 800 million rows

by

The OP probably already knows this, but here is how to get the answer, disregarding efficiency. First, cards per location, as described in the comments: SELECT locationid, COUNT(DISTINCT cardID) FROM table GROUP BY locationid Next the same thing per state. SELECT substring(locationid, 1, 2) state, COUNT(DISTINCT cardID) FROM table GROUP BY substring(locationid, 1, 2) For … Read more

[Solved] How to get last 3 month name in sql server

by

Sorry @Imrul Kaesh , there is some mistake on the query. I didn’t filter the last 3 months by ItemId. I have updated my query as below, please have a try: WITH Last3Month AS ( SELECT DISTINCT TOP 3 MONTH(IssueDate) AS Mth FROM Issue WHERE ItemId = 452 –Please add this WHERE Clause ORDER BY … Read more

[Solved] How to get a column name in SQL Server? [closed]

by

1. Get column name in SQL Server For example: SELECT table_name=sysobjects.name, column_name=syscolumns.name, datatype=systypes.name, length=syscolumns.length FROM sysobjects 2. Example to execute the query in PHP $firstname=”fred”; $lastname=”fox”; $query = sprintf(“SELECT firstname, lastname FROM friends WHERE firstname=”%s” AND lastname=”%s””, mysql_real_escape_string($firstname), mysql_real_escape_string($lastname)); $result = mysql_query($query); solved How to get a column name in SQL Server? [closed]