Occasionally there may be the need to remove the date from the end of a string. This can be accomplished by using PATINDEX along with SUBSTRING. We will use PATINDEX to find where the date begins and then SUBSTRING to remove the date from the end of the file. We will use ClientName03302010.csv and we want to get ClientName.csv from that to process. PATINDEX requires an expression to search for and an expression to search from that we will be returned a number, the location of the searched for ex...
Read More
As you can see we have two tables, with the same values. Joining on these tables is no problem at all. Simple, right? Well, take a look at what happens when we try to join on the column containing the NULL values. The results are the following: As you can see, we are missing a row. The row containing the NULL values did not get picked up by our join. We should have all three rows in the result set. The join statement does not deal with NULL values well when joining. So we can use ISNULL to repla...
Package Configurations in SSIS are key to making your packaged portable across the entire development process. The problem you being to face with configuration files is the quantity that you collect. Recently I was working on a project where we had pacakge level configurations as well as a configuration for each connection. In order to to updates on this would have taken a very large amount of time, especially since I had to make configuration files for dev and production. I opted to go with con...
There are a number of ways that you can calculate dates using T-SQL. A common practice is to find the first day of the week which can be accomplished easily by using the following function. select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) However, you will notice that this returns the date for Monday as SQL Server uses Monday for the start of the week. If your week starts on Sunday or you need to get the date for the previous Sunday you can simply modify the DATEADD part of the query from adding ...