Help Required Converting numeric date to Real Date YYYYMMDD

[wildsharkuk]

Hi,

I'm strugling converting a numeric date in the format yyyymmdd to a date format.

With the help of Tek-Tips i've got my hands on the formula to convert the number to a date but it doesn't handle dates like 30/02/2004 etc.

the formula is:

if Length(ToText({FIELD_DATE_NUM}, 0, "", "")) = 8 then

Dim nbrYear as Number
Dim nbrMonth as Number
Dim nbrDay as Number

Dim dtmDates as Number

dtmDates = {FIELD_DATE_NUM}

nbrYear = Truncate(dtmDates / 10000)

If Truncate((dtmDates - (nbrYear * 10000)) / 100) > 0 and Truncate((dtmDates - (nbrYear * 10000)) / 100) < 13 then

nbrMonth = Truncate((dtmDates - (nbrYear * 10000)) / 100)

else

nbrMonth = 01

end if

If Truncate((dtmDates - (nbrYear * 10000)) - (nbrMonth * 100)) > 0 and Truncate((dtmDates - (nbrYear * 10000)) - (nbrMonth * 100)) < 32 then

nbrDay = Truncate((dtmDates - (nbrYear * 10000)) - (nbrMonth * 100))

else

nbrDay = 01

end if

formula = CDate(nbrYear, nbrMonth, nbrDay)

end if

the above formula is in basic syntax and I am using Crystal reports 10 reporting on a SQL server 2000 database.

unfortunately the application was written by a third party vendor and adds so many days to a previous numeric date field eg 20050130 becomes 20050230 in the field I need to report on, my formula will not accept 20050230 as this is not a valid date and halts the report.

if anybody has any help on this matter, it will be greatly appreciated.

many thanks

wildsharkuk

 

[lupins46]

So what do you want to happen if the data is invalid?

 

[wildsharkuk]

hi lupins46

if possilbe i would like to return the last day of the month eg if date equates to 30/02/2004 then return 29/02/2004.

thanks

wildsharkuk

 

[lupins46]

Add the following code bits:

At the top of the routine, with the other Dims:
Dim X as Date

At the end of the routine change :
nbrDay = 01

end if

formula = CDate(nbrYear, nbrMonth, nbrDay)

end if

To this:

nbrDay = 01

end if

x= dateserial(nbrYear, nbrMonth, nbrDay)

if day(x) = nbrday then
formula = x
else
formula = dateserial(nbrYear,nbrMonth+1, 0)
end if

end if

 

[wildsharkuk]

thats perfect, works a treat thanx for your help lupins-46

cheers

wildsharkuk