How can I group by date time column without taking time into consideration
Cast/Convert the values to a Date type for your group by.
SQL
Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot
I know this is an old question. The way I solved it – after failing by increasing the length or even changing to data type text – was creating an XLSX file and importing. It accurately detected the data type instead of setting all columns as varchar(50). Turns out nvarchar(255) for that column would have … Read more
Is there a coalesce-like function in Excel?
This is an Array Formula. After entering the formula, press CTRL + Shift + Enter to have Excel evaluate it as an Array Formula. This returns the first nonblank value of the given range of cells. For your example, the formula is entered in the column with the header “a”
MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
As far as I can tell, both syntaxes are equivalent. The first is SQL standard, the second is MySQL’s extension. So they should be exactly equivalent performance wise. http://dev.mysql.com/doc/refman/5.6/en/insert.html says: INSERT inserts new rows into an existing table. The INSERT … VALUES and INSERT … SET forms of the statement insert rows based on explicitly … Read more
GROUP BY and COUNT using ActiveRecord
Distinct and Group By are going to give you different results. To get the results you expect you’ll want to use Seen above, group will return things as a hash. While distinct just returns the number of people in total, seen below.
MySQL OPTIMIZE all tables?
You can use mysqlcheck to do this at the command line. One database: All databases:
Foreign key references invalid table
Please see in this SQLfiddle link, Link
Solutions for INSERT OR UPDATE on SQL Server
Assume a table structure of MyTable(KEY, datafield1, datafield2…). Often I want to either update an existing record, or insert a new record if it doesn’t exist. Essentially: What’s the best performing way to write this?
LEFT function in Oracle
There is no documented LEFT() function in Oracle. Find the full set here. Probably what you have is a user-defined function. You can check that easily enough by querying the data dictionary: But there is the question of why the stored procedure works and the query doesn’t. One possible solution is that the stored procedure is … Read more
The server principal is not able to access the database under the current security context in SQL Server MS 2012
Check to see if your user is mapped to the DB you are trying to log into.