Tag: sql-server

Extract Date from a file name

I have a column called FileName and I wanted extract the date from the column as a Date column. FileName M:\Mapping\Workforce_Planning\ABC\ABClrmp.full.20160107.csv Desired Result Date 2016-01-07 I wanted to know the most efficient way. Thank You in advance

When to use a negative index for substring

When does it make sense to call SUBSTRING with a zero or negative index, considering the same result can be achieved with an index of 1. These queries produce identical results. They truncate the rightmost character. But why does TSQL even produce a result for the 3rd, 4th and 5th queries. SELECT LEFT(‘ab,’, LEN(‘ab,’) – […]

Running as a windows user in Visual Studio Team Services

I need to build a Visual Studio project in Team Services and then run some tests. The tests make REST API calls and then verify that the REST API calls worked by directly accessing the database. The database is a Microsoft SQL Server database that only allows for Windows Authenticated users to access it. It […]

How to design database tables for hierarchical data with unknown depth?

I want to create hierarchical data with unknown depth like this: Create categories and sub categories and for those subcategories they will have also subcategories and so on. The depth of the subcategories is unknown and only will be done in the runtime by the user. What I though about is to but them all […]

Unable know the exception in query

I’m writing the below query in SQL Server update time_tracker set logout = GETDATE(), totaltime = SUBSTRING(CONVERT(varchar(20), (LOGOUT – LOGIN),120),12,8) from Time_Tracker where userid = 0138039 and CONVERT(Date, LOGIN) = CONVERT(Date, GETDATE()); Basically, when the user hits a logout button I’m trying to achieve the below. Enter the logout datetime stamp in my logout column […]

How to check email for file using c#?

I receive an email with an excel file every week. I know there are probably better ways to accomplish my goal, but would it be possible to have a script task in SSIS that can open email, look for a specific file name as an attachment and then copy that file to another location? Here […]

Getting GUID of recently inserted row

Consider these two SQL Server tables Person (PersonId uniqueidentifier, PersonName varchar(20)) Hobby (HobbyId int identity(1,1), PersonId uniqueidentifier, HobbyName) My user interface takes the person name and list of hobbies as input. When user submits the form, I would like to insert appropriate records in the two tables. For e.g. insert into Person values(NEWID(), ‘John’) I […]

query to update data in a table in SQL

I have a lookup table that has following data: AlcoholStatus Id Name 0 None 1 Not Drunk 2 Drunk Less 3 Drunk Medium 4 Heavy Drinker Another table reference this table data (ID) and so it acts as a foreign key in another table. Now I want to update this Table to the following with […]

Determine Non-Matching Item in Set

I have tables similar to this: #1 OriginalID | Area ========== | ==== 2 | Abdomen 3 | Abdomen #2 Area | Part ==== | ==== Abdomen| Abdomen Bottom | Bottom #3 Part | OriginalID ==== | ========== Abdomen| 2 Bottom | 3 Desired result: OriginalID | Area | Part ========== | ==== | ==== […]

Creating a trigger in SQL Server for the appropriate table based on the logic

I have an InventoryOnHand table with a Quantity column. When an InventoryTransaction record is INSERTED, I need to update the Quantity in the InventoryOnHand table. Can someone please explain the following: Should the trigger created in the InventoryOnHand table or the InventoryTransaction table? I would assume the InventoryTransaction table since this is the ‘INSERTED’ When […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.