Changing a character date field to a date field

Brian Element

Forums

Date fields can come in many different varieties.  Visual Basic has a function called CDate that is able to figure out the majority of the formats and translate them into a common format.  The following is the code for a custom function to do the change.

Option Explicit
Function CtoDate(p1 As String) As Date
    ctodate = CDate(p1)
End Function

sunlox Wed, 10/29/2014 - 19:23

I think this doesn't work if you're importing using SQL server and the dates come as Numeric in a format that IDEA can't understand. You'll use these and other formulae but you'll end up with "Error" for each record of the field. Currently my problem, but I've had to export my databases to Access where I can fix that.

mpine@clearbal… Thu, 10/25/2018 - 13:15

I need to change a character field that has both MM/DD/YYYY and MM/D/YYYY to a date field.  the @ctod function doesn't work for the 10/1/2018.  Any help would be appreciated.

Steven Luciani Thu, 10/25/2018 - 15:01

Hello,

The attached text file contains the equation you need. Open the text file and copy and paste the contents into the equation editor of IDEA.  It uses @ctod, but also uses @getat and @insert to insert leading zeroes for months and days that are missing them.

You will need to use the equation editor's find/replace button. 5th from the left on the equation editor toolbar to replace my generic field name with your date's field name.

The return for this equation is mm/dd/yyyy. This can be tweaked to dd/mm/yyyy if you happen to come across that format as well. Simply change the date mask inside the quotation marks at the end of the equation.

Also, if your separator in the field is something other than /, say a hyphen -, you can also use find and replace to edit the separator.

Cheers,

Steve