DateTime field from YYYYMMDDHHMMSS string 1

[Reebo99]

Dear All,

As you might have guessed, I'm currently working on a number of reports using different date formats.

I've come up with the following formula to convert YYYYMMDDHHMMSS string to a Crystal DateTime value.

All you guru's who came up with this years ago, please accept my apologies for boring you....All those who, like me, have spent hours taking the mid values, converting them to numbers and building the datetime, Enjoy!

My test date shown is 1st January 2003, 23:59:59

datetime(date(picture(left("20030101235959",8),"xxxx,xx,xx&quot),time(picture(right("20030101235959",6),"xx:xx:xx&quot))

The above formula can be used for any format, YYMMDDHHMMSS, YYYYMMDDHHMM, etc. with simple changes to the left and right functions, and the picture format:

YYMMDDHHMMSS
datetime(date(picture(left("030101235959",6),"20xx,xx,xx&quot),time(picture(right("030101235959",6),"xx:xx:xx&quot))

YYYYMMDDHHMM
datetime(date(picture(left("200301012359",8),"xxxx,xx,xx&quot),time(picture(right("200301012359",4),"xx:xx&quot))

Any comments would be appreciated, including if anyone has a better way of doing this.
Reebo
Scotland (Sunny with a Smile)

 

[chelseatech]

I would use the built in DTS function and use Picture to add the punctuation to make the string a DTS formatted date.

DTStoDateTime(
Picture(("20030101235959","xxxx/xx/xx xx:xx:xx&quot)

The fewer funcions must be faster. Two instead of your six. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Reebo99]

ChelseaTech,

I agree, and I thank you. Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

Let me forewarn you that using this function in a record selection criteria may cause Crystal to NOT pass the SQL to the database.

-k

 

[Reebo99]

Personally, I'm using it purely for display, DateDiff and DateAdd formulas.

No problems to date, but well worth the comment synapsevampire. Reebo
Scotland (Sunny with a Smile)