Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Eliminate zeros from Date conversion 1

Status
Not open for further replies.

kchaudhry

Programmer
Nov 10, 2003
256
US
Hi,

I am using CR 8.5 with SQL server 2000. I am using the following formula for date conversion.

cdate(val(mid({Participant_Move_in_Trans.Reservation_Date},3,2)),
val(mid({Participant_Move_in_Trans.Reservation_Date},5,2)),
val(mid({Participant_Move_in_Trans.Reservation_Date},7,2)))

I am not sure how to check for zeros and null values. Can someone please help me with this?

Thanks in Advance.

Kchaudhry
 
Checking for 0 might not be a good idea for years if your dates might include 2000 since you apparently have a 2 digit year (strange).

Post details such as the current date format when posting.

Try:

isdate({table.field})

it may be in a proper format for this already.

Or you can format it like this:

if isdate(
mid({Participant_Move_in_Trans.Reservation_Date},5,2)+"/"
mid({Participant_Move_in_Trans.Reservation_Date},7,2))+"/"
mid({Participant_Move_in_Trans.Reservation_Date},3,2))
) then
cdate(val(mid({Participant_Move_in_Trans.Reservation_Date},3,2)),
val(mid({Participant_Move_in_Trans.Reservation_Date},5,2)),
val(mid({Participant_Move_in_Trans.Reservation_Date},7,2)))

But check your date format again to see that you can't use left({table.date},4) for the year to assure precision (really helps with data entry errors).

-k
 
Synapsevampire,

Thanks for your reply. I really dont have a choice here. My company wants me to use two digits for year "01". They do not have data for 2000.
The field is a string [8]. It was showing up as 1/09/3. I just right clicked on it and created a custom style to show up as 01/09/03.

Secondly I tried the formula you have given in the post. It gives me a message ")" is missing and the cursor is blinking after this portion of the formula
mid({Participant_Move_in_Trans.Reservation_Date},5,2)+"/"
if I add the parenthesis it gives me a message "then" is missing.

I am not sure what to do.

Kchaudhry
 
Sorry, try:

if isdate(
mid({Participant_Move_in_Trans.Reservation_Date},5,2)+"/"+
mid({Participant_Move_in_Trans.Reservation_Date},7,2)+"/"+
mid({Participant_Move_in_Trans.Reservation_Date},3,2)
) then
cdate(
val(mid({Participant_Move_in_Trans.Reservation_Date},3,2)),
val(mid({Participant_Move_in_Trans.Reservation_Date},5,2)),
val(mid({Participant_Move_in_Trans.Reservation_Date},7,2))
)

Rushed the post.

-k
 
Synapsevampire,

Thanks. Its seems to work fine. I will have to make some changes to the data to verify.

You have been a great help.

Kchaudhry
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top