SQL Snippets

Introduction

This post is going to be a work in progress.  It is (well it will eventually be) a collection of cool little T-SQL snippets I have used over the years.

First Day of Current Month

DECLARE @StartOfCurrentMonth DateTime

/*This Portion Gets 1st of Current Month */
SET @StartOfCurrentMonth = DATEADD(DAY, 1, GETDATE() – DAY(GETDATE()) + 1) – 1

/* Test the outcome */
SELECT @StartOfCurrentMonth AS [StartOfCurrentMonth]

/* if you are confused like me – break it down */
SELECT ‘STEP 1 | ‘ AS [Step], GETDATE() – DAY(GETDATE()) AS [Data]
UNION
SELECT ‘STEP 2 | ‘ AS [Step], GETDATE() – DAY(GETDATE()) + 1 AS [Data]
UNION
SELECT ‘STEP 3 | ‘ AS [Step], DATEADD(DAY, 1, GETDATE() – DAY(GETDATE()) + 1) AS [Data]
UNION
SELECT ‘STEP 4 | ‘ AS [Step], DATEADD(DAY, 1, GETDATE() – DAY(GETDATE()) + 1) – 1 AS [Data]

And the results look like this:

StartOfCurrentMonth
———————–
2012-08-01 13:33:28.090

(1 row(s) affected)

Step      Data
——— ———————–
STEP 1 |  2012-07-31 13:33:28.090
STEP 2 |  2012-08-01 13:33:28.090
STEP 3 |  2012-08-02 13:33:28.090
STEP 4 |  2012-08-01 13:33:28.090

(4 row(s) affected)

SQL & DateTime

Introduction

I started out as a clipper developer in the 90’s.  Clipper??  What the heck is Clipper you say?  You know those “old style” languages where you had to “code” your own screens 🙂 No such things as WinForms in clipper. The database I was using at the time was Pervasive Btrieve, which was a flat file based database. So DB access at the time, if you didn’t seek first against the key (yeah, indexes did exist) was:

1 – Open file
2 – Read while Not EOF
3 – Test each record for a filter match (NO select * from MyTable where Field = Filter)
4 – If the Record matched your criteria, you would do something with it.

The awesome thing was that it was extremely fast, and mind you, the boys at Pervasive were kind enough to provide a SQL API a few years later that you could use from the next gen WinForms style apps to fire SELECT statements at Pervasive.

Be that as it may, while looping through the data, testing if a Date field matched your criteria, the results were very consistent with what you would expect! i.e. If you were looking for all records in the date range 01 Jan 2012 to 31 Dec 2012 your code would test

      
  Param1 = 01 Jan 2012
  Param2 = 31 Dec 2012

  if Date >= Param1 and Date <= Param2 
    DoSomething()

And you would actually get ALL the records.

So….what’s all the fuss?

SQL & The “Dreaded” DateTime

There are probably more elegant ways of solving this tiny issue than how I chose to get around it, but I am hopeful I will help some devs out there who rely heavily on unit tests. Lets be honest, if you dealing with millions of rows of data, how would you really know if you missed a couple? You would probably write a test script like:

 
  Param1 = 01 Jan 2012
  Param2 = 31 Dec 2012

  SELECT COUNT(*) 
  FROM MyTable 
  WHERE (Date >= Param1) AND (Date <= Param2)

To compare your output to. The problem here is, they would yield the same result, but both would be wrong.

If you take a SQL date like 2012-12-31 16:07:28.320…this record would not be included in your result set. Why? Because it is actually greater than 31 Dec 2012!

How I got around it was to add 1 day to my End Date and modify the filter as below:

 
  Param1 = 01 Jan 2012
  Param2 = 31 Dec 2012
  NewParam2 = Param1 + 1 Day = 01 Jan 2013

  SELECT COUNT(*) 
  FROM MyTable 
  WHERE (Date >= Param1) AND (Date < NewParam2)

Conclusion

When working with the DateTime field, take extra special care to ensure you get ALL the data you are looking for, and always check the data around the start and end dates to ensure you have everything. I picked this up many years ago, only because I was expecting payments to have happened on the last day of the year and didn’t have any records in my resultset to match that date.

Read Excel files in .NET – The DataReader way

Introduction

A few years ago, an application I was working had a requirement to import an excel spreadsheet into SQL.  I had written tons of office interop code in Delphi and C#, but all of them always created spreadsheets from data, never really the other way around.

My 1st stab at doing it (the lazy way), was to create a SQL 2000 DTS package which imported the spreadsheet into a SQL Table and then executed the package from code (SQL 2000 implementation).  This needed the DTS libraries installed on each of the target machines the application would be used on as well as special DB privileges for each of the users allowing them to execute the DTS package.  Not ideal.

So when I got around to converting the system from Delphi to .NET, I decided to give the Excel reading thing a go. I mean, .NET is awesome….right?  It must have some really cool libraries for doing this kind of thing….and I have also never really been a big fan of using COM / Office interop for working with Office either and an Excel worksheet looks just like a SQL table too doesn’t it?

So I wanted to read the excel sheet like i would when using a DataReader.

      
  var recordList = new List<MyRecordStructure>();

  using (var reader = db.ExecuteReader(System.Data.CommandType.Text, 
      "Select * from SomeTable"))
  {
    while (reader.Read())
    {
      var rec = new MyRecordStructure()
      {
        Field1 = DBHelper.GetDecimal(reader, "ActualPrice", 0),
        Field2 = DBHelper.GetDecimal(reader, "ModelVestPrice", 0),
        Field3 = DBHelper.GetDecimal(reader, "ModelPrice", 0),
      };
      recordList.Add(rec);
    }
  }

After some google searching, I came across this awesome little library called Excel Data Reader which can be downloaded here.

 

Using the Code

First of all, you need to define your class which maps to your spreadsheet.

Id	Code	Date	Price	Discount
1	ABC	01-Mar-03	100.49	10.49
2	DEF	01-Mar-03	100.49	10.49
3	GHI	01-Apr-03	100.74	10.74
4	JKL	01-Apr-03	100.74	10.74
5	MNO	01-May-03	100.99	10.99

  

  ///<summary>
  /// Maps to the actual record in the excel sheet    
  ///</summary>
  public class MyRecordStructure   
  {
    public int Id { get; set; }
    public string Code { get; set; }
    public DateTime Date { get; set; }
    public Decimal Price { get; set; }
    public Decimal Discount { get; set; }
  }

Now all you need is a Stream with an Excel file:

 
  FileStream stream = File.Open(@"C:\File.xlsx", FileMode.Open, FileAccess.Read);

Then you add your boilerplate DataReader code and let the Excel Data Reader library perform all the magic (please remember to add a reference to the library you have downloaded)

 
  var recordList = new List<MyRecordStructure>();

  using (var reader = ExcelReaderFactory.CreateOpenXmlReader(stream))
  {
    while (reader.Read())
    {
      if (skipped)
      {
        var rec = new MyRecordStructure ()
        {
          Id = reader[0].ToInt(),
          Code = reader[1].ToString(),
          Date = reader[2].ToDateTime(),
          Price = reader[3].ToDecimal(),
          Discount = reader[4].ToDecimal()
        };
        recordList.Add(rec);
      }
    }
  }

Conclusion

So if you pressed for time and need to read data from an Excel sheet, try the Excel Data Reader library as it is really easy to use and Excel is not required on the Target machine.  It also supports reading excel files from ’97 – 2007.

Why am I here?

AND NO!!!!

This is not a post about the philosophical question concerning the meaning of life or existence in general, but more about why I decided to start blogging.

As developers, we spend a fair amount of time finding solutions to problems.  The remainder of our time, we spend implementing the solutions we have found (and maybe we throw in a few fleeting thoughts about documentation 🙂 for good measure).  Invariably, we at some stage in the future end up looking for something we have already implemented but can never quite remember in which application it was or even where we saved it.  So, we start the entire process again.  Google -> Test App -> Implement + Months Go By-> Misplace

Hence “Why am I here!” was born. It is a collection of thoughts and solutions to issues I have faced and how I managed to solve them (and not misplace them :)).  Through my efforts, I am hoping to be as helpful to the development community as the other awesome devs out there whose blogs I have learnt so much from and where I have found many answers.

Happy reading!