Useful T-SQL Dates Reference

For short US datetime format use this:

select convert(varchar, getDate(), 1) --10/15/2017

For a list of most others (SQL Server), see: https://rauofthameem.wordpress.com/2012/09/14/sql-date-time-conversion-cheat-sheet/

On to the useful SQL dates:

select dateadd(mm, datediff(mm, 1, getDate()), 0) as FirstaTheMonth

select dateadd(ms, -3, dateadd(mm, datediff(m, 0, getDate()) + 1, 0)) as LastaTheMonth

select dateadd(qq, datediff(qq, 0, getDate()), 0) as FirstDayOfQtr

select dateadd(wk, datediff(wk, 0, dateadd(dd, 6 - datepart(day, getDate()), getDate())), 0) --(First Monday of Month)

Great T-SQL References:

http://www.gehbeez.com/sql-get-first-and-last-day-of-a-week-month-quarter-year/

http://brianvanderplaats.com/cheat-sheets/T-SQL-Cheat-Sheet.html

TDD in small, digestible chunks

Test-Driven Development becomes (necessarily) much more common as code bases grow larger, diverse and more mission critical. Every piece of logic that can be explained in your software's documentation should be testable.

Write the tests, then write the code to meet those conditions, refine, repeat

Though 100% code-coverage is rarely attainable, the closer to fully covered with unit and integration tests, the more secure and solidly structured the code will be. Moreover, the dependency nightmares that many teams wrestle with will be largely mitigated as code reliability checks take place daily when TDD is fully embraced and implemented well.

I am assuming you know the basics of "why" and "how" TDD is done. If TDD does not feel natural, you may want to check out the book Test-Driven Development by Kent Beck. Reading that, what was most impressionable to me and has aided my TDD development style are the following quotes:

"Return a constant and gradually replace constants with variables until you have the real code". -on testing complex functions or objects

 

 "Write tests you wish you had. If you don't- you will eventually break something while refactoring". -on code bases with limited test coverage.

Do not write a few large tests to evaluate multiple ideas/functions at once. Write many small tests that test something that can be said in one short sentence: "Password Reset form successfully sends Reset email to appropriate user".

Write all the tests you know you will need and the tests you wish you had. You will wind up with quite a lot of code, but also quite a lot of "proofs", and you will be able to more confidently add to and refactor your highly-maintainable TDD-developed code.

Python 3 for fun - Screen Scraping

Download Python 3: https://www.python.org/downloads/

Open cmd, type: python

from lxml import html
import requests

#Initialize your page and tree list variables
page = requests.get('http://econpy.pythonanywhere.com/ex/001.html')
tree = html.fromstring(page.content)

See your page variable's output:

(Excerpts taken from Python.org, I've updated the instructions for Python 3+)

#This will create a list of buyer elements from the DOM (divs with a specific 'title' attribute):
buyers = tree.xpath('//div[@title="buyer-name"]/text()')
#This will create a list of price elements from the DOM (spans with a specific class):
prices = tree.xpath('//span[@class="item-price"]/text()')

print ('Buyers: ', buyers)
print ('Prices: ', prices)


Expected output:

You can get a lot of useful things done in Python with relatively few lines of code.

SQL NULL cannot be applied to arithmetic operations (select NULL > 1 = NULL)

"Any arithmetic expression containing a null always evaluates to null. For example, null added to 10 is null." -http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements005.htm#i59110

In SQL Server, this behavior can result in string concatenation failing due to one of the values being NULL, which, as is NULL's nature, turns the entire variable into NULL.

....results in: NULL

Two easy ways to work around* the inability to evaluate NULL are as follows:

 (COALESCE takes first non-NULL value specified whereas ISNULL takes just one alternate value)

Different database platforms have different rules for NULL but arithmetic ops will not work in any, as NULL is only ever NULL, and NULL is an unknown. If you are checking for the existence of data, you should be using:

select top 5 m.Column1 FROM MYTABLE m WHERE m.Column1 IS NOT NULL

*you can do this in lieu of not getting bad data in the 1st place, obviously- if the column in question needs to hold a legitimate numeric value for calculation.. your app probably should not be generating and storing null values into that column.

Invisible characters from pasted data

If you find yourself debugging an ETL task error where the problem at hand is data type mismatch or some other data error that is not visible to you in the source (culprit) data, don't fret.

What has likely happened is invisible formatting characters got into the data.

To remedy things, all you'll need to do is open up Notepad++, paste your data into the text window, and from the View menu dropdown, select "Show Symbol" >> "Show All Characters".

Simply edit the data in Windows Notepad (remove the special characters), save, and try to import again. Your data should be sufficiently scrubbed clean and your ETL task will return to normal.

There is also a shortcut button in Notepad++:

PS: handy tip for Windows users- to remove all formatting from copied data, paste the text into Notepad. All formatting will be removed from the data. Re-select it and go paste where needed.

PureText is another application that handles removing all formatting from text: https://stevemiller.net/puretext/