I a long string like below in my column and I need to extract only the date (2021-07-05) from it. Could anyone please help? Answer We can use PATINDEX with SUBSTRING here: Demo The call to PATINDEX above finds the starting position of the date, while SUBSTRING takes 10 characters from that position.
Tag: sql-server
Triggers data retention in inserted table and combination of update and insert on one column
I have a table in SQL Server which houses snapshots of all data to track changes in status of the source table tbl_D_project. If this status changes or a new record is added in tbl_D_project, a trigger should start and the line will be copied to the snapshot table. I wrote two triggers based on another questi…
How can I check if the ID’s of a table match another ID’s of another table in SQL
So I have 4 parameters, one of them its given as an input parameter of a stored procedure (its like a code that represents an unique action), and I need to match these ID’s with another ID’s in another table, so I need to check in table 2 the ID’s “DeliverTypeID”, “Schedule…
Iterate through a list of servers in SQL Server
I have a script similar to below and get: Could not find server ‘@CURSERVER’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. I am trying to get SQL Server Agent jobs from all linked s…
Parsing JSON from SQL Server
I have a table with the following columns, one column being a JSON blob. I’m unclear how to parse the JSON blob as a series of columns alongside the other columns. I know there’s something called OPENJSON, but not sure how to apply it to this case. The json string example is below, from the DATA c…
How to make a separate column for each condition using one sql query?
I have a table with the data in the following format: It contains the number of seconds each user has watched tv on a specific day. One user can have more than one watching session per day. For example, user 1 has watched tv three times on Monday. What I need to do is to create a separate column for
SQL copying data with new foreign keys
I have a very tricky problem here. I have two tables. One is dependent on another by foreign key. Table 1 Table1ID Instance ID ModifiedBy 1 1 yevhen 2 1 yevhen 3 1 yevhen Table 2 ID Instance ID Table1ID 1 1 1 2 1 2 3 1 3 These are taken as datasets for something named “Scenario”. When somebody
Getting values based on other column
I have the following data in SQL. Is there a way using SELECT QUERY that we can replace the NULL values in DATE column based on the REF values? Like replace the NULL values with the first available date for matching REF value, without making any change to the database. Expected Result Answer You can do it wit…
Get the first record from Table 2 where timestamp is greater than timestamp in Table 1
I have two tables. EVENT_NARATIVE EventNum SEQ Message Message_Time GRADE_HISTORY EventNum Grade GradeChangeTime The EVENT_NARATIVE Table has a lot of data (millions of rows) and each Event (EventNum) can have multiple EVENT_NARATIVE records. The GRADE_HISTORY table contains thousands of rows and each EVENT c…
SQL Server syntax correction [closed]
Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers. Want to improve this question? Update the question so it’s on-topic for Stack Overflow. Closed 9 months ago. Improve this question I am trying to create a SQL Server stored procedure but I am getting…