Month manipulation 4

[Scott24x7]

Hi All,
I think I'm missing something probably obvious, but I've been reading the help file for a while and I can't find a function I'm looking for (or a clever way to do it).

I have some data that I'm importing, and the date is expressed like this:

25 June 2018
2 February 1965
19 December 2012

(etc...)

I noted the DMY() function also expresses a format in this way, so for today's date if I issue DMY(DATE()) it would give 29 June 2018.

That's all fine, but I want to store the date into a date field, and it rejects the spelled out month.
I'm sure I could create a cross reference (Like "January = 1", "February = 2", but that seems cumbersome.
Is there some function that will manage a spelled-out date?


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[atlopes]

No, not that I know of, there isn't, at least not directly.

A one-liner using the DATE() function (multi-lined for readability):

m.StrDate = "25 June 2018"
? DATE(VAL(GETWORDNUM(m.strdate, 3)), ;
       VAL(STREXTRACT("january:1:february:2:march:3:april:4:may:5:june:6:july:7:august:8:september:9:october:10:november:11:december:12:", ;
         GETWORDNUM(m.strdate, 2) + ":", ":", 1, 1)), ;
       VAL(GETWORDNUM(m.strdate, 1)))

 

[Olaf Doschke]

Dates are never stored with text, when you output a date, that may have a month name, but when you have a char date, that's not always inversely convertible.

The Date and Datetime types are binary and just like Integers are bianry 4 bytes, not human readable
And just like when you output an int you don't get the ASCII chars of the 4 bytes, but a humany readable number, you also always get a human radable date.

See it that way, when you write ? DATE(), what VFP really does is more like ? DTOC

(DATE())

But that does neither mean you can store dates in that text format into date columns, nor you can convert such texts to the date format with CTOD().

An example of what does NOT work:

Set Date AMERICAN
Set Date LONG
? Date()
? Dtoc(Date())
? Ctod(Dtoc(Date()))

Both ? DATE and ? DTOC(DATE()) output the same string, that means when you thing DATE() itself is that string, no, that ALWAYS is some bianry format you'll never see on screen.
And in case you output a date in long format with month name, that can't be converted back to this binary (native) date format. Even though CTOD() normally is the inverse of DTOC(), it doesn't invert everything, in this case you get the empty date.

So to conclude, you have to parse and give the month meanings yourself. What could help you is CMONTH(DATE()), but an array of 12 elements is perhaps more helpful,

Bye, Olaf.






Olaf Doschke Software Engineering

https://www.doschke.name

 

[Scott24x7]

Ok, thanks it confirms everything I tried (and failed) on. I have a small code table that I already have the full month names spelled out, so that I can use them in a drop down. I just added a second field (monthvalue), and then I just use it as a lookup. I just had hoped there was some inverse function, but nope, that's fine. I have it working now.


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."

 

[Tore Bleken]

[pre]Function month2int
Lparameters lcText
Local lcMonths,lnMonth,lnDummy
lcMonths='JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC'
lnDummy=At(lcText,lcMonths)
If lnDummy>0
lnMonth=Int((lnDummy+3)/4)
Else
lnMonth=0
EndIf
Return lnMonth[/pre]

 

[Scott24x7]

Hi Tore,
That's compact. Nice one.


Best Regards,
Scott
MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."