How to remove invisible characters in t-sql?
The ASCII code for tab is 9; you could try update tablename set columnname = replace(columnname, char(9), ”)
t-sql
SQL problem with error “Invalid data type”
I know this is an old post but since I encountered the same issue and was able to solve it, thought of sharing it. This is an IntelliSense cache issue and could be solved by pressing ctrl+shift+R (shortcut for Edit -> IntelliSense -> Refresh Local Cache)
Removing a default constraint after adding a new column in code first
Personally I can see nothing wrong with the first approach. Yes, you have to create a default constraint to add a non-nullable column to a non-empty dataset. And yes, you have to delete it afterwards if you need to make sure the new column is always added explicitly in the future, as per your requirement. … Read more
Multiple argument IF statement – T-SQL
You are doing it right. The empty code block is what is causing your issue. It’s not the condition structure 🙂 DECLARE @StartDate AS DATETIME DECLARE @EndDate AS DATETIME SET @StartDate = NULL SET @EndDate = NULL IF (@StartDate IS NOT NULL AND @EndDate IS NOT NULL) BEGIN print ‘yoyoyo’ END IF (@StartDate IS NULL … Read more
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Year)
Try using a format file since your data file only has 4 columns. Otherwise, try OPENROWSET or use a staging table. myTestFormatFiles.Fmt may look like: 9.0 4 1 SQLINT 0 3 “,” 1 StudentNo “” 2 SQLCHAR 0 100 “,” 2 FirstName SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 0 100 “,” 3 LastName SQL_Latin1_General_CP1_CI_AS 4 SQLINT 0 4 … Read more
converting Epoch timestamp to sql server(human readable format)
Use the DATEADD function: SELECT DATEADD(ss, 1291388960, ‘19700101’) …specifying a date of January 1st, 1970. In this example, it was provided in the YYYYMMDD format. DATEADD will return a DATETIME data type, so if you have a table & column established — you can use the function to INSERT/UPDATE depending on your needs. Provide details, … Read more
How do I select records less than 1 year old?
select * from MyTable where MyDate > DATEADD(year, -1, GetDate())
How do I remove non-breaking spaces from a column in SQL server?
Try using REPLACE UPDATE Your_Table SET Your_Column = REPLACE(Your_Column, NCHAR(0x00A0), ”) WHERE Id = x
When using multiple WHEN MATCHED statements, do they all execute, or does only one get executed?
To answer your question, yes, it will only run a single match and then break. However, if you’d like to have logic to allow for conditional matching in the update, the CASE statement is rather useful for this. Something like this as an example: MERGE INTO YourTable USING (VALUES (1, 1, NULL), (0, 0, NULL), … Read more
What are the main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE)?
Will OPTION(OPTIMIZE FOR UNKNOWN) reuse cache instead of recompiling each time? Yes, it will. There are two main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE) as can be seen from this quote from MSDN: OPTIMIZE FOR UNKNOWN Instructs the query optimizer to use statistical data instead of the initial values for all local variables when … Read more