Nevermind…. public class MainClass { public static void main(String[] args) { java.util.Date utilDate = new java.util.Date(); java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); System.out.println(“utilDate:” + utilDate); System.out.println(“sqlDate:” + sqlDate); } } explains it. The link is http://www.java2s.com/Tutorial/Java/0040__Data-Type/ConvertfromajavautilDateObjecttoajavasqlDateObject.htm
SELECT DISTINCT a,b,c FROM t is roughly equivalent to: SELECT a,b,c FROM t GROUP BY a,b,c It’s a good idea to get used to the GROUP BY syntax, as it’s more powerful. For your query, I’d do it like this: UPDATE sales SET status=”ACTIVE” WHERE id IN ( SELECT id FROM sales S INNER JOIN … Read more
There are mainly three types of variables in MySQL: User-defined variables (prefixed with @): You can access any user-defined variable without declaring it or initializing it. If you refer to a variable that has not been initialized, it has a value of NULL and a type of string. SELECT @var_any_var_name You can initialize a variable … Read more
If you can’t just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values: select count(case Position when ‘Manager’ then 1 else null end) from … You can also use the sum aggregate in a similar way: select sum(case Position when ‘Manager’ then … Read more
PRAGMA table_info(table_name); will get you a list of all the column names.
You can’t alter the existing columns for identity. You have 2 options, Create a new table with identity & drop the existing table Create a new column with identity & drop the existing column Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you … Read more
I don’t believe SQL Server has a built-in split function, so other than a UDF, the only other answer I know is to hijack the PARSENAME function: SELECT PARSENAME(REPLACE(‘Hello John Smith’, ‘ ‘, ‘.’), 2) PARSENAME takes a string and splits it on the period character. It takes a number as its second argument, and … Read more
I like CTEs and ROW_NUMBER as the two combined allow us to see which rows are deleted (or updated), therefore just change the DELETE FROM CTE… to SELECT * FROM CTE: WITH CTE AS( SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7], RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1) FROM dbo.Table1 ) DELETE FROM … Read more
A great post handling several cases, from simple, to gaps, to non-uniform with gaps. http://jan.kneschke.de/projects/mysql/order-by-rand/ For most general case, here is how you do it: SELECT name FROM random AS r1 JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM random)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC LIMIT 1 This supposes … Read more
MySQL has a concept of user-defined variables. They are loosely typed variables that may be initialized somewhere in a session and keep their value until the session ends. They are prepended with an @ sign, like this: @var You can initialize this variable with a SET statement or inside a query: SET @var = 1 … Read more