I’m trying to update my date column which is (eg. 2012-04-20) format in into mm-yyyy. I searched for solutions that you can do by converting to varchar however it gave me an error Msg 241, Level 16, State 1, Line 4 Conversion failed when converting date and/or time from character string. This is the cod…
Tag: sql-server
How to use IS NOT NULL on a case when?
I have the below SQL query in SSMS where I want to exclude NULL rows that show up in the ‘intercompany budget’ columns. I’ve tried to search for answers and putting a WHERE clause for ‘Intercompany Budget’ IS NOT NULL, like in the query below, but NULL rows still show up. Any sug…
SQL Select One Record over another based on column value
What I am trying to do is select rows based off of a ‘priority’. Say I have this: I want to choose one row each for the control ids (the whole row), which would be the third row, because there is no priority, and the 2nd row becuase it has priority. So if I have two control IDs that are
How to sum and pivot a table in SQL?
I have the following table: col_a col_b col_c 1 0 1 0 1 0 I want to sum up each column and assign a value to it based on the output, with the output like-so below: column sum string col_a 1 ‘string one’ col_b 1 ‘string two’ col_b 1 ‘string three’ I’ve tried the follow…
Using SQL, when there are multiple columns with dates. how can I determine and return a row where the date is between the other rows?
I am building a table for a data warehouse that needs to have a row for each change that occurs. The issue is that there are sometimes changes that occur in the subgroups and I can’t figure out how to show those changes. For example, I have the following table: RowNumber Code CorrectedProductYear Produc…
T-SQL :: Error converting data type varchar to numeric when changing 1 to 0.5
I’m writing a complex T-SQL query with CASE that will help me calculate how much time it will take me to migrate databases based on the size of each database: All good, it works when I set 1 hour of work for every database which has less then 100.00 MB. Also all other values are summed and in the Hours
How to split an array of objects in SQL?
I have a table in my database which has the following columns: ID Data 1 [{“na”:false,”answer”:1.5},{“na”:false,”answer”:0.5}] 2 [{“na&…
How to make multiple aggregate function without break / divided the fields
Is it possible to make an aggregate function without break / divided the grouping fields? I make a query but it will divided into duplicate value in the first field, here is my query: My expected result: Emp. Name Count of Prod Total Account % Prod Duration Trip Emp.1 62 63 98,41% 30 Emp.2 45 48 93,75% 28 Emp…
SQL Server 2017 Convert Varchar to Date
I have a date stored as nvarchar which I cannot seem to convert to a date. I have tried the following but get the error message Conversion failed when converting date and/or time from character string Any ideas here? Answer Find the values that are causing the problem using try_convert(): Once you see what va…
Input selected row and ignore duplicate row
I have a problem that I need to insert a selected row from the table to another table based on last_update data or last_transaction_product. last_update is the data updated from the table, and last_transaction_product is the latest transaction from my shop, and then my row has duplicate data but has a differe…