I’m trying to figure this out, but it’s driving me insane! How do I calculate in SQL the number of days that are in a specific year between two dates? For example: we have the range date [12/30/2016-01/05/2017]. If I need to find how many days are in 2017 between this range of dates, it would be 5…
Tag: sql-server
Return all values from objects using OPENJSON()
Here is my problem: My JSON may vary based on API response and there is no guarantee that “shortTitle”, for example, can be present in my JSON next time when there is a post request. I have to figure how to “loop” (if that’s the correct term) through JSON to get all fieldID and c…
Using JSON_VALUE for parse column in SQL Server table
I have never worked with JSON in SQL Server before that’s why need some help. I have written a simple snippet of code: In sample above I write code how get first “id” from “tags”. But how looks like script if in “tags” not 2 “id”, but an unknown number thi…
Finding latest timestamp per value across columns and rows
I have a database table with rows like Where ID is the non-unique identifier for the rows and will be used to filter the initial dataset. Fields s1 to s6 contain values and columns ts1 to ts6 contain their timestamps. And I need to find the latest (or first) timestamp per “s” value. The “s&#…
Table “already exists” after dropping with if exists
I got a pretty complex SQL that finally forced me to use a temp table to work around. Essentially it looks like this: However, I get an error message saying an object with the name #MYTEMPTABLE already exists in the database after a call with an error (which is rather likely if the customer/tester screws up s…
Like in dynamic function
The code below works well. I however have issues trying to turn it into a like statement that I need some assistance with So far, I have tried the code below but with not much success I have also tried; Answer If your stored procedure parameter is @deliverer and your dynamic SQL parameter is @pt, I believe yo…
How can I view an earlier version of a table on a T-SQL server?
I’ve just done something very silly. In both the productive and development environments on our SSMS server, I’ve changed the wrong column in one table. I know exactly what rows have been changed, but not what their original values were. I did not use a transaction. Is there any way to view the hi…
SQL Server ‘AS’ alias unexpected syntax
I’ve come across following T-SQL today: that yields following result: The part that got me confused was d(c) While trying to understand what’s going on I’ve modified T-SQL into: which yields following result: It was clear that d & m are table reference while letters in brackets c & b…
Remove String and Symbols
I am trying to create a query to remove some varchar and symbols from a big string, basically a table will have a column with this format(the information comes from an API call): so the requirement is to take the main “column names” from the sample, so at the end the string will be like: this shou…
How do I update the additional fields from 1 table based on the data from another table?
I have 2 temp tables #tempbackfilltable and #temptablelive. The backfill table has more entries than the live table. After cross referencing both tables and getting the additional entries using this code: I want to update one of the columns (DBTimestamp) in #tempbackfilltable using existing data in #temptable…