[Solved] I’m looking for a way to trim a string so that only 6 consecutive digits show

You’re close! You need to use PATINDEX instead of CHARINDEX. After that, you can use SUBSTRING to pull the six numbers out. This should work for you: Select SUBSTRING(d.Notes, PATINDEX(‘%[0-9][0-9][0-9][0-9][0-9][0-9]%’, d.Notes), 6) From YourTable d If there are records in your table that do not have six consecutive numbers, you can use either of the … Read more

[Solved] How to convert the Varchar of sum to time using sql server 2008 [closed]

Try this: DECLARE @SUM VARCHAR(50)=’33.90′; SELECT CAST(CAST(LEFT(@SUM,CHARINDEX(‘.’,@SUM,0)-1) AS INT)+ CAST(SUBSTRING(@SUM,CHARINDEX(‘.’,@SUM,0)+1,LEN(@SUM)) AS INT)/60 AS VARCHAR(10))+’.’+ CAST(CAST(SUBSTRING(@SUM,CHARINDEX(‘.’,@SUM,0)+1,LEN(@SUM)) AS INT)%60 AS VARCHAR(10)) result: 34.30 1 solved How to convert the Varchar of sum to time using sql server 2008 [closed]

[Solved] Generate column Alias name dynamically in MS SQL Server [closed]

create procedure my_procedure (@col_alias varchar(100)) as declare @my_stmt nvarchar(max) = N’select 1+1 as ‘ + @col_alias exec sp_executesql @stmt = @my_stmt exec my_procedure @col_alias=”[This is a dynamix col alias]” This is a dynamix col alias ————————— 2 15 solved Generate column Alias name dynamically in MS SQL Server [closed]

[Solved] Stored procedure to find first day of month when an End Date and number of month differences is given in sql server

Use a tally table to generate N number of dates DECLARE @TillDate DATE = ‘20151031’ DECLARE @NoOfMonthFromStartDate INT = 5 ;WITH Tally(N) AS( SELECT TOP(@NoOfMonthFromStartDate) ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) FROM sys.columns ) SELECT DATEADD(MONTH, -(N-1), DATEADD(MONTH, DATEDIFF(MONTH, 0, @TillDate), 0)) FROM Tally If @TillDate is always the end of a month: ;WITH Tally(N) AS( SELECT … Read more

[Solved] How do I pivot with hour format(HH:HourNumber)?

First of all create a temp table to use it in 3 places – Select columns for pivot, Replace null with zero and inside pivot. SELECT DISTINCT SUM(ORDERTOTAL) OVER(PARTITION BY CAST(ORDERTIME AS DATE),DATEPART(HH,ORDERTIME)) [TOTAL], CONVERT(varchar, CAST(ORDERTIME AS datetime), 103) [DATE], DATEPART(HH,ORDERTIME) [HOUR], ‘HH:’+CAST(DATEPART(HH,ORDERTIME) AS VARCHAR(3)) [HOURCOL] INTO #NEWTABLE FROM ORDERTBL ORDER BY DATEPART(HH,ORDERTIME) Now declare … Read more

[Solved] SQL: Column invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [closed]

Introduction SQL is a powerful language used to query and manipulate data stored in databases. It is important to understand the syntax and structure of SQL queries in order to ensure that the query is valid and produces the desired results. One common issue that can arise when writing SQL queries is the error “Column … Read more

[Solved] How to select data from the following table?

select category_name,parent_category_name from tbl_product p left join ( select cp.category_code, case when cp.parent_code = 0 then ” when cp.parent_code != 0 then cp.category_name end as category_name, cp.parent_code, isnull(ch.category_name, cp.category_name) as parent_category_name from tbl_category cp left join ( select cl.category_name, cl.category_code, cl.parent_code from tbl_category cl ) ch on cp.parent_code = ch.category_code ) as cat on cat.category_code … Read more

[Solved] sql qry trickl puzzle

SELECT * FROM @tblA WHERE @ColA = ColA AND (@ColB = ColB) AND (@ColC = ColC) Union –Handles direct or wrong input values SELECT * FROM @tblA CPT WHERE @ColA = ColA and (EXISTS(SELECT 1 FROM @tblA WHERE @ColB=ColB) and @ColB is Not NULL and @ColB = ColB) Union –Handles direct or wrong input values … Read more

[Solved] convert sql server file into mysql [closed]

The constraint lines in you output may define the constraints, eg: ALTER TABLE [dbo].[Message] WITH CHECK ADD CONSTRAINT [FK_Message_Message] FOREIGN KEY([OpratorID]) REFERENCES [dbo].[Oprator] ([ID]) says there is a relationship between Message and Operator ( Message.OperatorID = Operator.ID ) 0 solved convert sql server file into mysql [closed]