Problems converting a string to a date

[#1]

Hi,

I am having a real issue with the conversion of a string field into a date. The original format of the field is 20090101 and I am attempting to use the Date function in the following way Date([Planned Settlement Date],'DD/MM/YYYY]') AS [Planned Settlement Date].....

However I am returning absolute rubbish for the year with the days and month looking correct i.e. 20090101 is coming out as 01/01/8474

Does anyone have any idea as to what maybe causing this????

Cheers

Kris

[jonathandienst · Accepted solution]

Kris

Use Date(Date#([Planned Settlement Date], 'YYYYMMDD'), 'DD/MM/YYYY]') AS [Planned Settlement Date]

Jonathan

[tresesco]

Hi,

Use MakeDate function as follows:

MakeDate(left([Planned Settlement Date],4),mid([Planned Settlement Date],5,2),right(left([Planned Settlement Date],2)) as [Planned Settlement Date]

hope this would help you. I didn't check Jonathan's one earlier...but yes that one is even easier.

Regards,

tresesco

[#4]

Thank for your help that worked perfectly