Sunday, August 16, 2015

MS SQL Partial Date

I have been struggling to find a best practice for storing partial dates as I build out an application that attempts to be as flexible as possible in storing information. A lot of information and data are not consistent on date requirements.

There are three different methods that I have thought to do. Another method which is the same way how MySQL implements dates where they use 0s for unknown date parts.

The first attempt was to create multiple columns. This worked ok until I had tables that required multiple date fields then they became cumbersome although could still work.

The next attempt was to use the date field and a bitmap column to identify which part is unknown. This seems cleaner.

I then attempted to research if there was a way to create a new type. I started looking into the user defined types but this did not appear to do what I needed it to do.

The last idea (not yet implemented) is to create another table to store partial dates, then use an id to create a link. This way I did not have to create multiple columns. I am not sure whether I will ever attempt this. The above two seem like a cleaner way to do the same thing.

I had also thought to use an int column to store the date with 0 for unknown parts, YYYYMMDD. For example, 101 will be January, 1st with unknown year; while 20150100 will be January 2015.

Different scenarios where I had the need for partial dates:

  • Birthdays - Users may or may not provide all the information. Year could be required for certain applications. For social media, users are less likely to provide year. Month or day could be the only information known from conversations without prying further.
  • Founded dates - Certain situations like publishers only give the month and year. Other times only the year is known.
  • Known dates - When tracking historical information from hearsay, only confident information can be used. For example when determining the start date of an employee, a person can recall that they started in a certain month but not the exact date.

Windows 10 (first impression)

Just upgraded yesterday to Windows 10 from Windows 7 Pro. So far the transition has been good. I have not had any issues continuing with working with Visual Studio and standard web surfing.

I haven't had to play around with any OS configurations yet. Glad that I haven't been forced to learn it yet. Didn't notice much of a difference with Microsoft's new browser Edge from IE (besides the cosmetics). I was kind of disappointed that the icon looks similar to IE.

W10 is supposed to be faster but I haven't noticed it being much different so far but then I haven't had any speed issues with W7. Not sure if I care that MS has returned to a more squarish (in some ways more windows-like) format. There are a lot of new settings which look interesting, but importantly not really out of the way.

I do like that do have a few more touchpad commands with sizing, shortcuts with multi-finger tapping. I did not like that it changed the scrolling to reverse scrolling so that the behavior was more similar to a touch screen, but this is changeable.