date compare problem 1

[kate8]

Hi,

I need to compare a date which is from database and data type is varchar to currentdate when select data.
The problem is the date field in database is varchar with format like yyymmdd, for example for today: 20101207. But the currentdate by default is 12/7/2010. I can’t change the format to the field in database before it is selected, so the only thing I can do is to change currentdate. I did ToText(), replace '/', after that I got 1272010. Or I can use Mid to reformat it to "2010127", but still won’t work since the field in database for today is "20101207".
How to make currentdate use double digits in month and day? I know you can do format field, but it doesn't change the default.

Sorry to bother twice in one day. Thank you for any helps!!!

 

[fisheromacse]

i believe you can create a formula that would convert the data to a date then use that in your select expert:

//{@MakeItADate}
Date(val(left({table.field},4)),val(mid({table.field},2)),val({right({table.field},2)))

 

[kate8]

Hi fisheromacse,
Thank you for the respond!!
But I got the error: A month number must be between 1 and 12 when I use the formula Mydate:
Date(val(left({table.field},4)),val(mid(mid({table.field},2)),val({right({table.field},2))).

I double check the database, the date field is yyyymmdd. Does "07","01" be considered 1-12?

 

[briangriffin]

You're getting bogged down in formatting - you don't need to format the date in any particular way, as long as it is converting properly to a date datatype. In this case you need to get it into the YYYY,MM,DD format only to take advantage of CR's Date function, which converts a text string into a date.

This is one requirement where the Picture function simplifies things greatly:

date(picture({textdatefield},"xxxx,xx,xx"))

 

[lbass]

Should be:

Date(val(left({table.field},4)),val(mid({table.field},[red]5,[/red]2)),val({right({table.field},2)))

-LB

 

[kate8]

Hi briangriffin & lbass,

Thank you so much!!!

It is working now after made change as lbass's suggestion!!

I also tried "date(picture({textdatefield},"xxxx,xx,xx"))", but got error: bad date format string.
This is my first time using picture(), very good to know it. Will try to make it work too.

Thanks a lot to everyone!!! I have got a lot of help from this forum. Thank you!

 

[fisheromacse]

thanks lbass!
i didn't double check my formula.

 

[lbass]

I like Brian's solution (*)--it should work as long as the text date is always 8 characters.

-LB

 

[kate8]

Hi,

Brian's solution works now!! Thank you!
I got the error because more than half of the records in that date field have no data. I added not is Null and not empty condition, then works great.
Again, thank you to all!!!