wildsharkuk
Programmer
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
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