Skip to content

MikeBevers.be

Putting it out there…

Archive

Tag: DateTime

Context

When you’re working with a UI or plain text data integration, conversions are often an issue. In this case I would like to talk about DateTime objects.

Recently, there was an issue in one of the other teams. They had a WinForms UI where they wanted to display a DateTime property in two different textboxes, one for the date and another for the time. They had their reasons for not using a DateTimePicker control or any other method. That wasn’t really the problem. One of the business users discovered an error when trying to save changes. The development team figured out what went wrong, apparently it was a “Culture” thing.

The business user’s computer was using Windows XP and its regional settings were set to English – United States. When the application tried to parse the textboxes and store the value back in the property, the month and day were swapped. In Europe we usually use “dd/MM/yyyy”, in the US they use “MM/dd/yyyy”.

Normally this should not pose a problem, the date was successfully loaded, so why wouldn’t it convert back as expected. Well, one of the main reasons is VB.NET. VB.NET has a feature called CDate, which can convert a string to a Date object. In C#, you only have a DateTime object. What happened? The CDate takes the regional settings and when you enter 16/10/2010, it will crash, because there is no 16th month.

Enter Mike

I overheard an intense discussion going on and decided to see if I could be of use. Call it interfering if you want; I see it as helping colleagues ;) . They explained the situation of the custom controls (the two textboxes) and the error. One of them was telling they needed to use DateTimePicker controls, another wanted to change regional settings in the database and application (I missed the point of that one), and so.

After a bit of evaluating, I noticed they were only complicating the solution/problem. I told them they only needed to parse the string into a DateTime object with specific format, and then assign the DateTime to the Date object. So instead of doing a lot of changes, add a single line of code…

I created a helper/utility class on the fly, so they could end their discussion and get back to work (and stop bothering the rest of us :) ).

DateTimeParser

using System;
using System.Globalization;

namespace MyNamespace
{
    public static class DateTimeParser
    {
        public static DateTime ParseDateTime(string value, string inputFormat)
        {
            return ParseDateTime(value, inputFormat, CultureInfo.InvariantCulture);
        }

        public static DateTime ParseDateTime(string value, string inputFormat, CultureInfo provider)
        {
            if (string.IsNullOrEmpty(value))
                throw new ArgumentNullException("value");

            try
            {
                return DateTime.ParseExact(value, inputFormat, provider);
            }
            catch (Exception ex)
            {
                throw new Exception("Could not parse date: " + value, ex);
            }
        }
    }

	public class DateTimeParserDemo
	{
		public DateTime Test()
		{
			string myDate = "31/12/2010 23:59:59";
			return DateTimeParser.ParseDateTime(myDate, "dd/MM/yyyy HH:mm:ss");
		}
	}
}

 

VB.NET code

If you prefer the code in VB.NET head to the website of DeveloperFusion.com, they have an excellent C# to VB.NET converter.

Context

Working with DateTime fields in MSSQL is not the same as you do in .NET. I found myself googling the same things over and over. That’s why I decided to group the things I need most in one blogpost. This way I don’t have to keep googling, I just need to browse my blog and voilà.

I will update this blogpost when I’m in need of different functionalities.

Comparing the date part of DateTime fields

You shouldn’t cast or convert your DateTime fields to text and then do a text compare like:

SELECT CASE WHEN CONVERT(varchar(10), getdate(), 101) = CONVERT(varchar(10), getdate(), 101) THEN 'Equal' ELSE 'Different' END AS 'Compare result'

You should use the DateTime methods provided by Microsoft:

SELECT CASE WHEN DATEDIFF(d, getdate(), getdate()) = 0 THEN 'Equal' ELSE 'Different' END AS 'Compare result'

The DATEDIFF method accepts a datepart argument. The method will base its result on the supplied datepart argument. More info: DATEDIFF on MSDN

Displaying DateTime fields

I found this example on the internet:

SELECT DATEADD(dd, -DATEDIFF(dd, getdate(), 1), 1) -- 2010-08-02 00:00:00.000

I don’t find it very useful to put this in a SELECT statement, maybe when you’re using subqueries to be sure it will be parsed as a DateTime. Again, use the provided methods. In this case it would be the Convert. It’s always nice to see an example result, so here it goes:

SELECT CONVERT(varchar(50), getdate(), 100) -- Aug  2 2010 10:40AM
SELECT CONVERT(varchar(50), getdate(), 101) -- 08/02/2010
SELECT CONVERT(varchar(50), getdate(), 102) -- 2010.08.02
SELECT CONVERT(varchar(50), getdate(), 103) -- 02/08/2010
SELECT CONVERT(varchar(50), getdate(), 104) -- 02.08.2010
SELECT CONVERT(varchar(50), getdate(), 105) -- 02-08-2010
SELECT CONVERT(varchar(50), getdate(), 106) -- 02 Aug 2010
SELECT CONVERT(varchar(50), getdate(), 107) -- Aug 02, 2010
SELECT CONVERT(varchar(50), getdate(), 108) -- 10:40:10
SELECT CONVERT(varchar(50), getdate(), 109) -- Aug  2 2010 10:40:10:870AM
SELECT CONVERT(varchar(50), getdate(), 110) -- 08-02-2010
SELECT CONVERT(varchar(50), getdate(), 111) -- 2010/08/02
SELECT CONVERT(varchar(50), getdate(), 112) -- 20100802
SELECT CONVERT(varchar(50), getdate(), 113) -- 02 Aug 2010 10:40:10:870
SELECT CONVERT(varchar(50), getdate(), 114) -- 10:40:10:870

Not that obvious

Setting a RowFilter that compare’s dates, isn’t quite the same as comparing numbers or strings. For starters Date values must be enclosed within sharp characters # #. The date format is the same as the result of DateTime.ToString() method for invariant or English culture.

Alternatively you can enclose all values within single quotes ‘ ‘. It means you can use string values for numbers or date time values. In this case the current culture is used to convert the string to the specific value. But we don’t always know, or want to know, in advance what the current culture is.

continue reading…