• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5375
  • Last Modified:

Use Crystal built-in function to convert YYYYMMDD string to date

Is there an easier way to convert a string representation of dates in format 'YYYYMMDD' and 'YYYYMMDDHHMM' than using the trim functions?
0
bsumida
Asked:
bsumida
  • 4
  • 2
1 Solution
 
mlmccCommented:
What are you trying to do?  DO you have date fields you are converting to strings?

mlmcc
0
 
bsumidaAuthor Commented:
The database that I am using stores dates as string in the form: YYYYMMDD because it is much more efficient when searching.

I am using an ODBC connection to the back end and can only get the date info in this form which I must translate to a more conventional date format, for example, using VB's midstr() function, to cut up the string and reformat. I would like to bye pass this extra parsing step, if possible
0
 
mlmccCommented:
Not really but you can do the entire conversion in a CR formula

Something like
Change D to your DATE field

cdate (left(D,4) & "/" & mid(D,4,2) & "/" & right(D,2))

For the DATETIME

datetime (tonumber(left(DT,4)),tonumber(mid(DT,4,2)), tonumber(mid(DT,6,2)),tonumber(mid(DT,8,2)), tonumber(right(DT,2)),0)

DATETIME requires (YYYY,MM,DD,HH,MM,SS)  since you don't have the seconds I added the 0.  If you have seconds use

datetime (tonumber(left(DT,4)), tonumber(mid(DT,4,2)),  tonumber(mid(DT,6,2)), tonumber(mid(DT,8,2)), tonumber(mid(DT,10,2)), tonumber(right(DT,2)))

good luck
mlmcc
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
mlmccCommented:
Do you need any more help?

mlmcc
0
 
bsumidaAuthor Commented:
Thanks mlmcc
0
 
mlmccCommented:
glad to help

mlmcc
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now