Convert varchar to date

[makk07]

Hi All,

I have a field for date in sql server database which is of datatype varchar and the data in it is in this format 20071031.

I am using this field in my report but I want the date in the format 10/31/07. Please anybody suggest me how to do that.


Thanks!

 

[lbass]

date(left({table.field},4),mid({table.field},5,2),right({table.field},2))

Then format the formula to the display you want by going to format field->date.

-LB

 

[makk07]

I tried this

date(left({lrs0186_Command.CUPED},4),mid({lrs0186_Command.CUPED},5,2),right({lrs0186_Command.CUPED},2))

But its giving me the error for the following code:

mid({lrs0186_Command.CUPED},5,2),right({lrs0186_Command.CUPED},2)

saying "Too many arguements have been given for this function"

Thanks for your help

 

[lbass]

I just tested this and it worked perfectly. Maybe you have a null or a string without all 8 numbers, so try:

if isnull({table.field}) or
trim({table.field}) = "" or
len({table.field}) < 8 then
date(0,0,0) else
date(left({table.field},4),mid({table.field},5,2),right({table.field},2))

-LB