ISO Date Formatting

As you might have heard, dates in SalesLogix need to be formatted as ISO dates for things to synchronize properly to remotes. To be clear, ISO formatted dates look like this:

To represent December 5, 2003 1:30:22 PM
20031205 13:30:22

The standard previously was ANSI, but this tends to have problems with Oracle databases & international date formats. ANSI dates look the same as ISO dates with the exception that the date parts are separated by dashes ‘-‘ (ie: 2003-12-05 13:30:22).

If you know me, you’d know I don’t enjoy working in the Architect. I do most of my work outside of SalesLogix using C# and integrating back to SalesLogix via the SalesLogix OLEDB Provider and COM (ClientObjix). So, let’s look at formatting those dates from a .NET language.

This task is made really easy using the built-in .NET format providers. Here’s some samples using ToString():

//current date/time
DateTime.Now.ToString("yyyyMMdd HH:mm:ss");
//or
DateTime dt = DateTime.Parse("1/1/2000 1:30:00 PM");
dt.ToString("yyyyMMdd HH:mm:ss");

You can also use string.Format so you can do the conversion inline, possibly with the rest of a SQL statement.

string sql = string.Format("insert into table (field, datefield) values ('{0}', '{1:yyyyMMdd HH:mm:ss}')", fieldval, DateTime.Now);

Easy enough, but I’d still prefer to not have to format my dates like this – every time I insert a row with a date in it. But I’ve already talked about that.