Convert String to Date

[Former Member]

I know there are a lot of threads like this one out there, but this has gotten the best of me.

I have the following formula, but it's saying that the Month should be between 1 and 12

My date field is in the format of : yyyymmdd and I want it to show as dd/MM/yyyy

Date(tonumber(right({Command.CheckDate}, 2)), tonumber(mid({Command.CheckDate},5, 6)), tonumber(left({Command.CheckDate},4)))

Thanks

[Former Member · Accepted solution]

You didn't say what's exact the original data type.

But it looks like string.

If it is Date, you can format it whatever you like.

So the question is "How to convert string to Date?"

You should check the two formula:

DTSToDate, CDate

My suggestion:

CDate(Picture({Command.CheckDate}, "xxxx/xx/xx")

Edited by: Bryan-Tsou on Aug 10, 2010 4:45 AM

[Former Member]

That's the way the dates are in an Eloquence database we have around here. This is a custom function I built to do exactly what you're wanting to do...

It's in Crystal syntax

Function  (numberVar DateField)
DateSerial(ToNumber(Left(ToText(DateField,"0"),4)), 
ToNumber(Mid (ToText(DateField,"0"), 5, 2)), 
ToNumber(Right(ToText(DateField,"0"),2)))

If you want to use it as a normal formula...

DateSerial(ToNumber(Left(ToText({TableName.DateField},"0"),4)), 
ToNumber(Mid (ToText({TableName.DateField},"0"), 5, 2)), 
ToNumber(Right(ToText({TableName.DateField},"0"),2)))

HTH,

Jason

[Former Member]

Date() function can only be used as Date(YYYY, MM, DD)

If you want date to display as DD/MM/YYYY you must format in the report display.

Date(tonumber(left({Command.CheckDate},4)), tonumber(mid({Command.CheckDate},5, 6)), tonumber(right({Command.CheckDate}, 2)))

Ian

[Former Member]

Date() function can only be used as Date(YYYY, MM, DD)???

Hey, please read the help carefully.

[Former Member]

I was referring o the way Nabilino was using the function

Date(DD, MM, YYYY) will not work.

Ian