Setting new Date Format 2

[PatMcLaughlin]

I am hitting my usual hurdle, working with dates. I have data which is downloaded from a SQL Database into a cursor. One of the fields contains a date field.
ex. [24-May-12 08:42:33]

I need send it out to another process as (mmddyyyy hh.mm.ss).

ex. [05242012 08.42.33]

Should be a piece of cake but not for me.

 

[PatMcLaughlin]

Please notice that the last example's month is 05242012 not 5242012. It must have the same number of characters in each date sent.

 

[Mike Lewis]

Pat,

A couple of questions:

1. What back end database are you using? You said it was a "SQL database", but that applies to virtually all databases.

2. What is the data type of the field within the database? Is it a character string, or datetime, or what?

If it's a datetime, then it should arrive in your VFP application as a datetime, in which case you can convert it to a character string in the desired format, as follows (where tDate is the value in question):

SET DATE AMERICAN
lcOutput = TRANSFORM(tDate)

* Get rid of the dashes
lcOutput = STRTRAN(lcOutput, "-", "")

* Change the colons (in the time element) to dots
lcOutput = STRTRAN(lcOutput, ":", "-")

I'm not sure about the square brackets around the value (in your example). If you need those, you can easily add them.

However, if the data in the database is a character string, and if it is represented as you show it (with the first three characters of the month name in English, and so), then it's slightly more complicated. But I suspect it's not really held like that; that's just the way you see when you view the database. Perhaps you could clarify that point before I try to give you a solution.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

One way to cope with the format problems of dates and other types is to delegate that task to ODBC.

For example a query like

Select * From remotetable WHERE datetimefield = ?ltDateTime

Works not only with MS SQL Server, but also with MySQL for sure and with many other databases, too. This mechanism of parameterisation is also the best against sql injection. Besides you can use the native foxpro datettime type.

So the initial question really is, what type do you get from the remote backend? If it is a string (likely it is, because I can't remember a date setting making Foxpro report months as three letter acronyms). What is the query you do, do you use conversion function for querying or is the data really stored as string? Because if so, you're just making life harder to yourself.

The rule of thumb is: Keep data in their native types as long as you can and only change to the human readability for the reason of human readability.

The type of string you want to send is rather in that direction, but you may also be able to again use ?ltDatetime to send a native format data and let the ODBC driver do the conversion as the remote backend understands it via:

Insert Into remotetable (datetimefield) Values (?ltDateTime)

In both cases you just need to set a ltDateTime Variable in Foxrpo to the desired datetime.

For example, with SET SYSFORMATS ON and with changing extended system settings for time and date formatting you can make Foxpro display DATETIME() as 25-May-2012 19.16.00, even with the unusual seperator characters.

Rule of thumb is, to stay in native date types as long and as best as you can.

If you nevertheless need that output format, let me correct Mikes little mistake in the last line:

tDateTime = DateTime()
SET DATE AMERICAN 
lcOutput = TRANSFORM(tDateTime ) 
* Get rid of the dashes 
lcOutput = STRTRAN(lcOutput, "-", "") 
* Change the colons (in the time element) to dots 
lcOutput = STRTRAN(lcOutput, ":", ".") 
? lcOutput

And code to get the dateparts from the input you get (with the three letter month short name) could be:

lcDateTime = "24-May-12 08:42:33"

* decompose into string parts
lcDay      = LEFT(lcDateTime,2)
lnMonth    = (AT(SUBSTR(lcDateTime,4,3),"JanFebMarAprMayJunJulAugSepOctNovDec")+2)/3
lcMonth    = TRANSFORM(lnMonth,"@RL 99")
lcYear     = SUBSTR(lcDateTime,8,2)
lcYear     = IIF(VAL(lcYear)<50,"20"+lcYear,"19"+lcYear)
lcTime     = SUBSTR(lcDateTime,11)

* finally convert these string parts to a native foxpro datetime via Evaluate() of a datetime literal:
ltDateTime = EVALUATE(TEXTMERGE("{^<<lcYear>>-<<lcMonth>>-<<lcDay>> <<lcTime>>}"))
? ltDateTime

Maybe you could shorten the code using more elegant expressions, but sometimes straight forward is the best to do to get more or less undertandable code.

Bye, Olaf.

 

[Olaf Doschke]

Just one more note on lcYear = IIF(VAL(lcYear)<50,"20"+lcYear,"19"+lcYear)

This is taking the idea from FoxPro's rollover year. You need to configure that rollover year as needed to decide when to interpret previous or current century (or next century later on in this century).

In this case the two digit year will be converted into the range of 1950-2049. Highering the 50 you can shift that. If your dates always will be current +/- a few days you could simply take the first two digits of YEAR(Date()), and concatenate the two digit year from the input datetime string.

Bye, Olaf.

 

[PatMcLaughlin]

Thank you both! I did in fact work it out before seeing these and I must be getting better, I did just about what you both said. I did learn a new command from you guys... Transform/StrTran(). I did it the hard way, STR(Year(timeField)), STR(Hours(timeField)) etc. then put them in order and with punctuation in another string, then passed the combined string. Long way around but I guess it worked.

 

[Olaf Doschke]

Transform is the general purpose function, which can take any type and transform it to a string. Foxpro - indeed any language needs to have something like that, as the display of any value of any type on screen typically first converts it to a human readable string. What ? prints to the string is transformed from the binary stored value via transform.

In this case TTOC(tDateTime) could also be used, as Transform(tDateTime) does not make any use of the further parameters, it's output is equal. Indeed the specific conversion functions are performing faster, also STR() transforms a number faster than TRANSFORM(), but on the other side with STR() you need to specify digits and decimals.

Bye, Olaf.