Removing or Replacing Bad Dates in Microsoft SQL

Sometimes you receive data from other sources that are suppose to be dates but are not entered correctly.  The roman classic I was born in ’12/15/0010′, or the time traveler ’03/23/99999′.  Sometimes its just junk like ‘tbd’ etc.  This is where try_cast or try_convert is essential.

Try_Cast or Try_Convert has been available since SQL 2008.  These will prevent an error to stop your query.

select TRY_CAST('1/1/2016' as date)
select TRY_CONVERT(date,'1/1/2016')

These will both successfully return a date of 1/1/2016.  But if you try to process the following:

select try_Convert(date,'1/41/2016')

This will return a null instead of an error.  You can wrap the try with a coalesce and return a default value

select coalesce(try_Convert(date,'1/41/2016'),'1/1/1900')

This will return 1/1/1900 and your query completes.

Advertisements

Dynamically Disabling Steps in SSIS

Often I find myself wanting to choose dynamically between different steps in  a package or being able to turn off a step without having to actually modify the procedure.

This can be accomplished dynamically by disabling a step via a variable or parameter.  In this example I have a loop that processes a set of college clubs.  The query loads a list of clubs.  I would like to have a different process for The Physics and Astronomy club.  Maybe eventually each club will have a different process of its own.  One way to make this happen is by controlling what is enabled.  Below is the package.

DisablePackageExample1

The SQL Load List of Files loads a table of the clubs to a source object that is used in the for each loop.   The Name of the club is assigned to  a variable called ClubName.

DisablePackageExample2

I then modify the step called Physics and Astronomy.  Open the properties of the Physics and Astronomy step and add an expression for the Disable feature.DisablePackageExample3

I test to see if the ClubName is not equal to Physics and Astronomy Club. If it isn’t then Disable is set to true and this step will not run.  If the ClubName is set to Physics and Astronomy Club then the step will continue.   Since I only have one other step for all other clubs.  I set the Disable for this step when ClubName is equal to Physics and Astronomy Club.  DisablePackageExample4

Now as I loop through the clubs,  the Physics and Astronomy Club step will only run for that club and All Other Clubs step will run for everything else.

Convert a date column to YYYYMMDD format

The YYYYMMDD format is very useful in organizing data for filenames, sorting, etc.

Here is one way to generate that  format.

SELECT CONVERT(VARCHAR(10), getdate, 112)

This is the method that works well in sql.  If you want to do the equivalent in an expression in SSIS then you can use the Year,Month,Day functions.

Year(getdate()) *10000 + MONTH(getdate())*100+ DAY(getdate())

This will result in a integer value in the form of YYYYMMDD.   You can then convert to a varchar.


Using Power BI Slicer with a Long List

Power BI Slicer is a great tool to customize reports, but it can be difficult to use with long lists.  One way to fix this is to use two slicers as a hierarchy to speed selection.

Here is an example.  The product list in AdventureWorks is long.  Here is a report that show the Inventory and the pricing for a product selected from the slicer.

singleslicer

The lit is long and its difficult to scroll up and down to find the product one is looking for.  One way to speed the search is to add a hierarchy.  I chose breaking things up by the first letter in the product name.

To do this, I edited the product table by copying the Name table.  Then i split the copy of Product Name into to two columns, with the first letter as the first column.  Then I renamed the column Name A-Z.

Name az

Now add a second slicer using the Name A-Z.  Now you can select the first letter of the product you want, then select the product from the filter product list!  Much easier than scrolling away!

cascading slicer

 

 

How to return a Zero instead of no Results in SQL Query

Sometimes you want to query to see if an item exists but if it doesn’t you usually get an empty result in SQL.  It would be more useful if it would return a 0.

I often want to ask the question: Does x exist? and get back a yes or no.  Yes or No often translates to boolean as a 1 or 0.  Let say I query to see if a Date is a Holiday.  I have a table of Holidays.

Holidays Table

I want to see if today is a holiday so i enter the following:

Select case when count(HolidayKey) > 0 then 1 else 0 end as IsHoliday
 from dbo.Holidays
 where date = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

The case statement returns the results I am looking for.   If the date is Holiday then the count of Holidaykey is greater than or equal to 1, which returns a 1.  If the date doesn’t exist in the table the count function returns a  zero.

count holidaykey

Without the count, the case statement fails to return the proper result. See below.

 

case HolidayKey

 

How can I move SQL Server Agent Jobs from one Server to Another?

You can move SQL Server Agent jobs from one server to another by scripting the jobs.  The best way is by opening up SQL Server Management Studio.

Select View Object Explorer from the  VIew Menu in SQL Server Management Studio.

Object Explorer Details

From the Object Explorer Details window select all the jobs that you want to copy. Right click and select Script Job As then Create To ,  New Query Editor Window.

Select Jobs

This will create one script for all the jobs.  This script can be saved then run on the new server.  This will recreate all of the jobs on the new server.