ISO Date Conversions Revisited

I’ve talked a few times in the past about converting dates to ISO format in SQL statements for the SalesLogix Provider (See “When to convert dates to ISO format“, “ISO Date Formatting“, “Date formats and the SalesLogix provider“). I cannot stress just how important this is. I guarantee you that if you’re not converting dates inline in your SQL INSERT/UPDATE/DELETE statements then you are having sync issues (even though you might not know it yet). When I get issues regarding synchronization back to me that is the first thing I will check.

I’ve posted before some code to demonstrate how to convert your dates to ISO formatted dates in C#. It didn’t occur to me that I never posted similar code for VBScript and legacy basic until I was asked the other day how to do this in a legacy script. I tend to focus more on C# since that is where I do most of my development. I don’t develop often in VBScript or legacy basic in the SalesLogix Architect but when I do I bundle in my own utility scripts which include reusable functions for ISO date conversions.

Converting dates to ISO in VBScript

Function ISODateTime(ByVal dtDate)

On Error Resume Next

    ISODateTime = CStr(Year(dtDate)) & _
    Right("00" & Month(dtDate), 2) & _
    Right("00" & Day(dtDate), 2) & _
    " " & Right("00" & Hour(dtDate), 2) & _
    ":" & Right("00" & Minute(dtDate), 2) & _
    ":" & Right("00" & Second(dtDate), 2)
End Function

With VBScript, your only option is to separate the date parts and put them back as a complete string. Not all that pretty, but gets the job done.

Converting dates to ISO in legacy basic

Function ISODateTime(ByVal dtDate As Date) As String
    ISODateTime = Format(dtDate, "YYYYMMDD HH:NN:SS")
End Function

SalesLogix Legacy basic includes the standard VB Format function which allows for a much cleaner syntax to format the string as an ISO date.