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?
LVL 4
koilaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
? LEFT(TTOC(CTOT('03/01/2013'),3),10)
0
Brian CroweDatabase AdministratorCommented:
Why is this listed in MS SQL Server?
0
Anthony PerkinsCommented:
I was asking myself the same question.
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

CaptainCyrilFounder, Software Engineer, Data ScientistCommented:
Probably because the author might be migrating data from one platform to another.
0
Olaf DoschkeSoftware 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:

SET CENTURY ON
SET MARK TO "/"
SET DATE DMY

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.
0
gdemariaCommented:
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
</cfquery>

  #dateFormat(myQuery.startDate,"yyyy-mm-dd")#


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)>
    #dateFormat(myQuery.startDate,"yyyy-mm-dd")#
 <cfelse>
   #myQuery.startDate# -- not a date, just display as-is
 </cfif>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.