Uncle Julio imported some new product codes into a database for the expanding Fajita business. However the leading zeros have been removed somewhere during the import process. The point of sale machines requires a 4 character product code so he turned to his close friend and I.T. expert Jose, to pad leading zeros in front […]
Quick snippet to generate a datetime value where you only have a year to work with. Creates a datetime value set to the 1st day of the year. This compilation of date possibilities is also really cool – though not as cool as Dan Aykroyd… http://www.sqlusa.com/bestpractices/datetimeconversion/
You have imported data from a text file into your database which included datetime values formatted as strings. An example is the following string ‘2013-09-12 01:03:52’ which represents the date 12th September 2013 at 3 minutes past 1am on the 52nd second. The following snippet example show how to convert this string to a datetime […]
A simple Percentage of Total Calculation example. If you are bringing your MDX dataset into SSRS remember SSRS doesn’t recognise MDX percentage values formatted with FORMAT_STRING=”Percent” – so you’ll need to specify the percentage format in your SSRS chart or tablix.
You want your months to display in your SSRS report as Jan, Feb, Mar etc yet you run the report and the ordering is all out of sorts. Adapt the following MDX sampler, to grab the month key, and follow the steps subsequent to set the ordering in your SSRS chart. The month key will […]
One frequently occurring scenario I come across is a bunch of records with the same identifiers but each row has a different datestamp – then I need to identify the latest record in each group. This is the end result of what needs to be achieved, I want a ranking for each record in the […]
You have a table that contains rows identifying start and end values for a range and you want to convert this to rows i.e. one row for every number between the start of the range and the end of the range. You also need to do it in a view. An example is : Id […]
Here is a small query to help you get a list of all tables and columns in your database, showing whether each column is nullable or not, and showing whether each column is part of the primary key or not. Enjoy!
The following example reseeds the identity value back to 1 for the table [schema]/[tablename]…this is useful for example after executing a truncate table where you need to reload the table from identity value 1 again :
You have a string that contains the value $1,230.23 and you want to convert to a money data type. The following works for SQL Server 2012.