Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


I have date as 23/04/2017 and I wan

I have date as 23/04/2017 and I wan

I have date as 23/04/2017 and I wan

I have date as 23/04/2017 and I want it to be in 23-Apr-2017 so that I export this is in .CSV file as a date format. How can I do this?

RE: I have date as 23/04/2017 and I wan

Lets start with...


I have date as 23/04/2017

HOW do you have the date?
* In a VFP Data Table Field of type DATE?
* Or in VFP Data Table Field of type CHARACTER?
* Or in VFP Data Table Field of type DATETIME?
* Or as a Memory Variable - Character string?
* Or how?
The reason I ask this is that it could be possible that your DATE is really in a 'standard' Date (MM/DD/YYYY) format and that it is only APPEARING like 23/04/2017 due to a SET DATE value in your code.
However if your 23/04/2017 is in a CHARACTER format, then that would not apply.

And when you want it to appear as:


* Will that be how the Text should appear within the CSV file?
* Or do you want that to appear in some Excel file AFTER the CSV file is opened in Excel?
* Or what?

With more detail we can assist you better.

Good Luck,

RE: I have date as 23/04/2017 and I wan

I agree with JRB. You need to give us more information.

That said, as a general guide, if your date is held in a variable or a field that is a Date or Datetime data type, and if you want to convert it to a character string in the format you specified, you can do something like this:

CODE -->

* Where ldDate is a Date or Datetime 
lcChar = TRANSFORM(DAY(ldDate)) + "-" + LEFT(CMONTH(ldDate), 3) + "-" + TRANSFORM(YEAR(ldDate))
* lcChar now contains the date in the required format 

If that doesn't meet your requirements, let us have the answers to the JRB's questions.


Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: I have date as 23/04/2017 and I wan

I just want to add that whatever you see might or might not be what you have.

If a VFP control or browse windows displays 23/04/2017 that doesn't mean that's what's stored. Dates are binary data, transformed to a human readable string. And other than string data types themselves, that's true for all data types, even integers are stored in four binary bytes and only transformed to up to 10 digits and a sign for human readability.

CODE -->

ldDate = DATE()
? ldDate
? ldDate
? ldDate 
This demos the same value is displayed different, the variable ldDate doesn't change. What changes though is, how you can work with data depending on what you have at hand. You can't determine LEFT(ldDate,4) (from YMD format), as a date is a date, not a string. To extract the four digits of the year - even if YMD format is set - you don't need LEFT(DATE(),4), you need YEAR(DATE()). That's why any advice to you will depend on what you really have at hand, not on what you see, and so JRBs need to know what you really have at hand because there is no one code fits all solution just because you describe you see 23/04/2017.

There also are multiple possible solutions, Mikes is just one. You can also make the System settings for a long date to display a three letter month abbreviation by choosing that as DD-MMM-YYYY and then display that in VFP via SET DATE LONG.

Your goal should be storing dates as date type and choosing whatever way suits you to get the desired output. It's recommended to not solve the problem with system settings, as those should remain users choice. On the one side you can respect users display wish at times you display for the user by SET SYSFORMATS ON, that's not only helpful for date display, also currency, time, numbers, anything having a locale the user mainly configures by choosing his region. In cases you need a certain format as output for data exchange, you can take control and for example do as Mike suggests, especially taking the first three letters of CMONTH() for the month portion. Be warned though, at design time within the VFP IDE this is English, at runtime, if you also provide local resource DLLs (VFP9rdeu.dll, vfp9rfra.dll etc) those will be other languages and that might fail. So when you want and need english month names, a safe option would be creating an array with laMonth[1]="JAN" to laMonth[12]="DEC" to use that to translate the numeric month to the month abbreviations.

Bye, Olaf.

RE: I have date as 23/04/2017 and I wan

To get the requested result, independent of the VFP date settings and according to the Windows date settings, so e.g. this will result for a pc with Dutch Windows settings the Dutch 3-letter month abbreviation. You may use this procedure:

CODE --> vfp

*!* GetFormatedDateByWindows
*!* See also: https://www.berezniker.com/
Lparameters TCDATE

? Transform(Day(m.TCDATE))+'-'+Proper(GetformatedDate(m.TCDATE,LOCALE_USER_DEFAULT,"MMM"))+'-'+Transform(Year(m.TCDATE))

Function GetformatedDate(tdDate, tnLocale, tvFlagsOrFormat)
	Local lcDate, lcFormat, lcDateStr, lnDateStrLen, lnFlags

	Do Case
		Case Vartype(m.tvFlagsOrFormat) = "N"
			lnFlags	 = m.tvFlagsOrFormat
			lcFormat = ""
			lcFormat = Null
		Case Vartype(m.tvFlagsOrFormat) = "C"
			lnFlags	 = 0
			lcFormat = m.tvFlagsOrFormat
			Assert .F. Message "Missing or Invalid 3rd parameter."

	* SYSTEMTIME Structure. Only Year, Month and Day members are relevant.
	lcDate = ;
		BINTOC(Year(m.tdDate), "2RS") + ;
		BINTOC(Month(m.tdDate), "2RS") + BinToC(0, "2RS") + ;
		BINTOC(Day(m.tdDate), "RS") + BinToC(0, "8RS")

	lnDateStrLen = 255
	lcDateStr	 = Space(m.lnDateStrLen)

	lnDateStrLen = GetDateFormat(m.tnLocale, m.lnFlags, m.lcDate, m.lcFormat, @m.lcDateStr, m.lnDateStrLen)
	lcDateStr	 = Left(m.lcDateStr, m.lnDateStrLen - 1)

	Return m.lcDateStr

Function GetDateFormat(Locale, dwFlags, lpDate, lpFormat, lpDateStr, cchDate)
	Declare Long GetDateFormat In win32api As GetDateFormat ;
		Long Locale, Long dwFlags, String lpDate, String lpFormat, String @lpDateStr, Long cchDate
	Return GetDateFormat(m.Locale, m.dwFlags, m.lpDate, m.lpFormat, @m.lpDateStr, m.cchDate)



Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close