[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] 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