?
Solved

Converting text field to a date field

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

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.

<CFQUERY name="AddData" DATASOURCE="FRED">
     INSERT INTO      EPOImport
                     (pk_EPOImport,
                         Validated)

        VALUES
                     (#EPOImportMaxId#,
                         '#Validated#')
</cfquery>
0
Comment
Question by:bobnims
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:TallerMike
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")
0
 
LVL 8

Accepted Solution

by:
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")>
<cfoutput>
     #comment#<br />
     #convertedDate#<br />
     #CreateODBCDateTime(convertedDate)#
</cfoutput>
0
 

Author Comment

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

Expert Comment

by:TallerMike
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:

([[:digit:]]{4})
([[:digit:]]{2})

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.
0
 

Author Comment

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

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

777 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