Tag: datetime

Oracle String SQL Query using a DateTime from C#

Posted by bsstahl on 2009-03-07 and Filed Under: development 

The following statement builds a C# string containing an Oracle SQL query that uses a DateTime specified in the .NET code.  It uses the Oracle TO_DATE function to convert the string date to an oracle date object and specifies the format of the string date for both the C# output and the Oracle input. Using this statement, there can never be a date format mismatch since the same format is specified at both ends.

string sql = string.Format("select * from MyDB.MyTable t where t.MyDate > 
     TO_DATE('{0:MM/dd/yyyy hh:mm}', 'MM/dd/yyyy HH24:mi')", DateTime.Now);

I have found myself using this type of statement fairly often to create tools that help me do my job.  For what it's worth, I wouldn't put this type of code in production since you rarely want to execute a query from a string in a production environment.

Tags: csharp oracle sql datetime dotnet 

Use System.DateTimeOffset To Better Handle Time Zones

Posted by bsstahl on 2008-12-25 and Filed Under: development 

In his post, The Death of System.DateTime? Omer van Kloeten evaluates the use of the System.DateTimeOffset object in place of System.DateTime to improve handling of Time Zone sensitive times.

When representing a date/time, especially in an internationally-faced system, you have to include a time-zone. DateTime did a very poor job handling time-zones, like being insensitive to changes. DateTimeOffset is the exact same thing as DateTime, only it takes heed of time-zones. For instance, comparing two DateTimeOffsets with the same UTC time in different time-zones will result in equality.

Moreover, DateTime also had only three modes: Local, UTC and Unspecified, whereas DateTimeOffset has an Offset property, allowing you to create dates in any time-zone you like.

I have not yet experimented with the System.DateTimeOffset type but what Omer says makes a lot of sense to me.  Has anyone else played with this object?

Tags: datetime type time zone