Import external data - convert string into date format

[#1]

Hi every body.

I'm importing data from an external MS ACCESS data source. In MS ACCESS table I have a field YEAR (format number).

By importing data into QV via script I need to convert this data into date format 1.12. & YEAR.

How can I do this ?

Thanx in advance

[Greg_Williams]

date(makedate(year, month, day),'MM-DD-YYYY')

[#3]

Thank you, Greg.

I tried

SQL SELECT .... date(makedate(YEAR,12,1),'DD.MM.YYYY') as MyDate ...

but no success 😞

Duski

[#4]

Please try like:

Date#(1 &'.'& 12 &'.'& Date_Year,'DD.MM.YYYY')

Attached with an example.

Edit: BTW do this in the Preload script not in the SQL Select section.

[Greg_Williams]

Use the wizard when importing, instead of straight SQL...it's much easier to manage...

Load

     <field>,

     <field2>,

     Year,

     date(makedate(YEAR,12,1),'DD.MM.YYYY') as MyDate

     ...

from source

[#6]

Thank you Ajay.

I tried

date#(1 &'.'& 12 &'.'& YEAR,'DD.MM.YYYY') - no success 😞

I tried

date#('1.12.'& YEAR,'DD.MM.YYYY') - no success too 😞

Duski

[#7]

Ok.You can try the following

• Load the table from the data base and then store it into a qvd (Just load like 10 records with like 3 or 4 relevant fields)
• Then drop the original table and now load this qvd and do the above mentioned function

Still if it doesn't work:

• If you can attach that qvd we could try to post a qvw with a solution

[Greg_Williams · Accepted solution]

Sometimes I have found that you need the `YEAR` around the field names. Have you tried placing the grave accent (`) (shares same key as tilde~) around the field name?

[#9]

Thank you once more, Ajay,

inserting the formula into tle LOAD section helped 🙂

See you

Duski