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 biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help Required Converting numeric date to Real Date YYYYMMDD

Status
Not open for further replies.

wildsharkuk

Programmer
Mar 12, 2004
36
GB
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
 
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
 


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


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

cheers

wildsharkuk
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top