Browsing the sql category

mysql update field to random string

I needed to update all fields in my database to a random value.
RAND() returns a random number between 0 and 1
MD5() creates an alpha-numeric hash, that is 32 characters.
I ended up with:

select MD5(RAND())

…You can add a SUBSTRING() on there to ensure you get only a certain number of characters.

Read the article

SSRS reports not showing null values

I had an SSRS report that was not displaying the right data.  I narrowed it down to, that the NULL values were not showing up; even though I did LEFT JOINs, and used the ISNULL() function in both the SELECT and GROUP BY clauses.  The issue though was with my WHERE criteria.

My criteria was something similar to:

WHERE field_name LIKE @parameter + ‘%’

The solution was to put the ISNULL() function in the WHERE clause!

WHERE ISNULL(field_name,”) LIKE @parameter + ‘%’

What made it harder to troubleshoot was, the SQL worked fine in Management Studio (it gave the expected results) … however only in Reporting Services was it not showing the right values.

Read the article

sql server query takes longer to run with parameters

(I also initially searched “sql query doesn’t run with parameters“)

I have a SQL query that I was running in Microsoft Management Studio and it took ~20 seconds to run.  It was a rather complicated query, with UNIONs and a Common Table Expression, and a Pivot.  But it ran.

I had some hard-coded values that, when I used parameters instead, the same query took too long to run (i.e. after 20 seconds, I gave up on it).

I changed my hard-coded values to be dynamic values, using this:

declare @first_of_this_month datetime;

declare @date_start varchar(20)

declare @date_end varchar(20)


SET @first_of_this_month = CONVERT(datetime, STR(MONTH(getdate())) + ‘/1/’ + STR(YEAR(getdate())));

SET @date_start = DATEADD(m,-5, @first_of_this_month);

SET @date_end =  DATEADD(m,10, @first_of_this_month);


And even though this runs in seconds,

select @date_start

select @date_end

select @first_of_this_month


the full query took much longer.


The solution: just add this to the end of the query:




Not really sure what it does, but it worked!  Note: this was for an SSRS report with two datasources. Even though the “long” query ran okay in SSMS and one query was instant, I had to add this onto BOTH queries (in each dataset) for it to work.

Read the article

SQL for Sunday-based Financial Period

At my company, we don’t use a “Calendar” period; we’ll use a “Financial” period.  The financial period  ends on the Sunday of the previous month.  This means that, for “March 2017,” the third period of the year will not end on March 31, but rather March 26.  This means that our “month” really started on February 27.  And the month of April will start on March 27.

There are some caveats; specifically you’ll never overlap years.  So January will always start on the 1st, and December will always end on the 31st – despite how  the Sunday falls.

I didn’t want to rely on maintaining a database table (with the individual years + the start/end of the financial period), so I have two SQL statements.

The “normal” approach (for most months) is to get the last Sunday of the previous month to determine the end; and add one day (Monday) to determine the start of the new period.  As previously mentioned, you have to watch out for a change in the year, or when you’re late in a month, and the start/end will differ.

As with all code I publish, there’s nothing company-sensitive here; this is just date manipulation:


Read the article

  • 03.09.2017
  • sql

SQL UNION query doesn’t work

I had a UNION-based SQL query that wasn’t working.   In addition to making sure that you have the same number of columns/fields in each query, try removing the ORDER BY clause.

I’ll have to Google this myself, but there’s a difference between UNION and UNION ALL …

Read the article

  • 02.22.2017
  • sql

Web Development for Intermediate Programmers – with PHP

I’ve written a ~300 page book on PHP!  While I do go over the basics, I go over “common” concepts of database manipulation and Ajax. I’ve also sprinkled in some User Interface and User Experience comments:

Read the article

mysql errno: 150

I went to add a Foreign Key to my MySQL database, and got an “errno: 150” error.

The solution: apparently it’s case-sensitive.  My table name begins with a capital letter, but the SQL had everything in lowercase.  Once I matched the cases, it worked.

Remember to create/drop  tables in the order that you need for it to work!

Read the article

  • 01.03.2017
  • sql

incorrect search results / Search Results giving all possible values

I had a SQL query (with Classic ASP, although it could really have been for PHP or any scripting language), that was incorrectly showing ALL results, and not what I had specified.  I had a lot of fields to search, so the query was similar to:

qry = qry & ” WHERE “

qry = qry & ”                       table.field_one LIKE ‘%” & search &”%’ OR “

qry = qry & ”                       table.field_two LIKE ‘%” & search &”%’ OR “

qry = qry & ”                       table.field_three LIKE ‘%” & search &”%’ OR “

qry = qry & ”                       1=1 “


I just copied-and-pasted that line for each field.  I wanted the lines to be consistent, so they all ended with “OR”.  But I needed something at the end of the statement – I can’t have an “OR” hanging at the end.  So I put “1=1”.  But this was causing a problem – because 1=1 is always true, so it was returning ALL results from the  database.

The solution is to NOT have  a consistent line throughout the SQL, and just leave the final “OR” off.

Read the article

  • 12.20.2016
  • sql

SQL Server INSERT (append) statement doesn’t work

I had a typical INSERT statement in SQL that followed the format


VALUES [values]

The problem was that I was inserting values from a SQL statement.  The “VALUES” clause is not needed:


SELECT * FROM [other_table]

Read the article

SQL – UNION example

It’s more efficient to have one really complex SQL query vs many queries.

You can combine two SQL statements with a UNION clause.

SELECT field_one, field_two FROM table_one


SELECT field_one, field_two FROM table_two

The (2) queries must have the same number of fields, and be in the same order (ex. you can NOT have “field_one, field_two” in the first query and then “field_two, field_one” in the second query.

This becomes even more useful when you use this query in another query; a “subquery” if you will.

Suppose you have a database that has employee labor charges.  You have one table for when an employee is out sick, and you have a separate table for when an employee is on vacation.  You want to write a query that combines – and sums – all hours entered into the database.  Using the UNION example above, you could have:

SELECT employee_name, SUM(hours) As HoursAbsent FROM


SELECT employee_name, hours FROM tblSick


SELECT employee_name, hours FROM tblVacation

) as qryMain

GROUP BY employee_name


This will sum all hours by employee. Note that I’ve given the combined queries an alias of “qryMain”.  I’m in essence using that as a table to query off of (SUM, and GROUP BY).

More Subqueries

You can also link a table to a subquery.  Assuming you have a field “field_a” in a table “tblOne” – you can link to a query that you create, giving an alias for the table/field name you are linking on:

SELECT tblOne.field_one, qryOther.field_two FROM tblOne



SELECT blah as field_a, field_two FROM tblBlah


SELECT foo as field_a, field_two FROM tblFoo

) as qryOther

ON qryOther.field_a = tblOne.field_a

Read the article

  • 08.02.2016
  • sql