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

[AMBRISHBHATIA]

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?

 

[JRB-Bldr]

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:

23-Apr-2017

* 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,
JRB-Bldr

 

[Mike Lewis]

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:

* 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

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

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.

ldDate = DATE()
SET DATE TO DMY
? ldDate
SET DATE TO MDY
? ldDate
SET DATE TO YMD
? 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.

 

[Koen Piller]

Hi,
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:

*!* GetFormatedDateByWindows
*!* See also: [URL unfurl="true"]https://www.berezniker.com/[/URL]
Lparameters TCDATE
#Define LOCALE_USER_DEFAULT	0x400

? 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
		Otherwise
			Assert .F. Message "Missing or Invalid 3rd parameter."
	Endcase


	* 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
Endfunc

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)
Endfunc

Regards,

Jockey2