How do I convert a character field

How do I convert a character field (e.i startdate 03/01/2013 (which is character) to a date format of yyyy-mm-dd (2013-01-03) ) in a .dbf table?
Who is Participating?
gdemariaConnect With a Mentor Commented:
If you are pulling the date string from the database and then using coldfusion to display the date in a different format, then it is an easy task.  ColdFusion will recognize the value as a date and you can just use dateFormat()

<cfquery name="MyQuery" ....>
   select startDate fromMyTable


Since it is a string column in the database, you can verify that the value REALLY IS a date with a simple test to avoid errors in case bad data gets in there..

 <cfif isDate(Myquery.startDate)>
   #myQuery.startDate# -- not a date, just display as-is
CaptainCyrilConnect With a Mentor Founder, Software Engineer, Data ScientistCommented:
? LEFT(TTOC(CTOT('03/01/2013'),3),10)
Brian CroweDatabase AdministratorCommented:
Why is this listed in MS SQL Server?
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Anthony PerkinsCommented:
I was asking myself the same question.
CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
Probably because the author might be migrating data from one platform to another.
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
CTOD() would be the function to use and how it is displayed in a browse simply depends on your date settings, a DBF does store a binary date format, not 'YYYY-MM-DD', that would just be another char value, no.

DBFs also don't store decimal numbers in int fields or in float fields, you mistake the display for what is stored.

To get CTOD() to work on strings like 'dd/mm/yyyy' you would need to set:


If you then browse a dbf or ? Date() the display also changes to dd/mm/yyyy, so what is displayed is not what is stored and what is stored doesn't matter much to you, as you only need to know you need to convert to type date or D.

So you do these three settings, then test if ? CTOD("01/10/2013") displays 01/10/2013. That doesn't look like a conversion, but it is, you can double check that in two ways:
1. ? VarType("01/10/2013") vs ? Vartype(CTOD("01/10/2013")), the fist will be C, the second D
2. SET MARK TO "." and then ? CTOD("01/10/2013") will convert to the empty date and display two dots only.

This also means conversion between char and date is very vulnerable. All your initial date strings must really be strictly formatted in the same way to be converted correctly, or you risc mixing day with month or getting empty dates from a conversion.

Take a look at help chapters about VARTYPE, about variable and field types descriptions in VFP, also about SET command and date settings and you'll see.

Bye, Olaf.

PS with / as date mark he more frequently used format is mm/dd/yyyy, but that may depend on your country, browse your data for dates with a day>12 to make sure which is day and which is month.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.