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
This answer has a lot of assumptions as you did not provide any logic for how you want your first table to get to your second. Here are the assumptions I made: A claim/Policy combo share a ID and are prefixed with P for policy and C for claim. The policy-date for a claim and … Read more
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]
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]
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
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
Since the date_issue is a varchar, it would be a problem with BETWEEN. Try changing the date_issue datatype from varchar to datetime 7 solved How to get data between two dates
SQL: Column invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [closed] solved SQL: Column invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [closed]
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
Sorry I was not realising that I aksed a bit too much. I actually figured it out on my own. So i guess I leave the code here in case anyone needs it. Have a nice day! #!/usr/bin/perl # PERL MODULES use strict; use DBI; use DBD::ODBC; use Text::CSV; use List::MoreUtils qw(each_array); # SOME VARIABLES … Read more