×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

Jobs

Month manipulation
4

Month manipulation

Month manipulation

(OP)
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."hammer

RE: Month manipulation

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

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

CODE --> VFP

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

RE: Month manipulation

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

CODE -->

 
(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:

CODE

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

RE: Month manipulation

(OP)
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."hammer

RE: Month manipulation

3
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 

RE: Month manipulation

(OP)
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."hammer

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