Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Converting text field to a date field

Posted on 2003-03-24
Medium Priority
Last Modified: 2013-12-24
I am pulling a text field which contains date info in the following format:

I want to write this back into my access database as a date time field via an SQL query.  What would be the cleanest way of doing this?  I have a couple ideas but they seem labor intensive.  Any suggestions would be greatly appreciated.

     INSERT INTO      EPOImport

Question by:bobnims
  • 3
  • 2

Expert Comment

ID: 8197063
Try this regular expression:

ReReplace(comment,"([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})([[:digit:]]{2})([[:digit:]]{2})([[:digit:]]{2})","\2/\3/\1 \4:\5:\6")

Accepted Solution

TallerMike earned 200 total points
ID: 8197077
A more complete example of it's use:

<!--- 2003 02 26 21 36 26 --->
<cfset comment = "20030226213626">
<cfset convertedDate = ReReplace(comment,"([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})([[:digit:]]{2})([[:digit:]]{2})([[:digit:]]{2})","\2/\3/\1 \4:\5:\6")>
     #comment#<br />
     #convertedDate#<br />

Author Comment

ID: 8197327
Thanks Taller Mike.  I definately need to learn more about expressions.  They seem like they can bequite powerful.

Expert Comment

ID: 8197432
With that said, lemme give you a quick lesson on what I did so you are sure to understand what I did:

There are 2 basic parts to this process, the matching of the fields, and the outputting of the fields. What we want to do is to match a certain number of numbers and then output it a different order. So I've created a bunch of 'match' elements which are denoted by (). Everything that is matched within the () is assigned to a variable named '\x' where 'x' is the match number. So the first set of () is matched to '\1' and the second is matched to '\2' and so forth.

So on the simple end I created 6 matches, and then reoutput them in a different order with some punctuation.

In your case there's only 2 different matches (the second is done multiple times) the 4 digit and 2 digit match:


As you can see each match is enclosed in () to make it a variable when matched. Then you'll notice the [:digit:] This matches any digit (this is the same as [0-9] matching any number). We enclose this is in [] because we want to dictate the number of them. If we left the extra [] out, it would only match one digit. To determine the exact number of digits we want, we surround that number in {}.

It would be read:

"Match 4 digits, followed by 2 digits".

Then finally on the replace end, we output the results of our replace with the \x variables:

"\2/\3/\1 \4:\5:\6"

The Regular Expression will simply replace the \x variable with whatever matched it. It looks a little more confusing that it really is in this case because you have forward slashes which look confusing next to the \x variables.

If you have ANY questions on this, please don't hesitate to ask.

Author Comment

ID: 8198593
Thanks Mike,
I can honestly say that makes total sense to me now.  It also works very well.  Thanks again,

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question