Date format from date field of xml file

[#1]

I am getting a field name date with data like 20080501 (yearmonthdate) from xml file; how to convert it into date format so i can use month(xxxxx) as Month, Year(xxxx) as Year in script while loading. Thanks for the answer.

[#2]

SELECT to_date(field_name, 'YYYYMMDD') As NormalDate

Would work in Oracle. At the very least, you could break it up into substrings and use whatever string to date function is available.

[rwunderlich · Accepted solution]

Use the QV Date#() function to read it and convert correctly.

date(date#(rawDate, 'YYYYMMDD')) as aDate

or

Month(date#(rawDate, 'YYYYMMDD')) as Month

For a working example, see the QV Cookbook sample "Reading non-standard dates". You can download the QV Cookbook from:
http://robwunderlich.com/Download.html

-Rob

[#4]

You could also parse the data if it is consistent and make it into a date.

makedate(left(datevalue,4),mid(datevalue,5,2),right(datevalue,2)) as dateconverted

[sathishkumar_go]

Hey use

MONTH

(date([fieldname],'YYYYMMDD')) as Month

and left(fieldname,4) as Year

[Oleg_Troyansky]

I used to use the "makedate" formula, like Marc suggested, but at some point it suddenly stopped working in one of my applications. Since then, I switched to date#() function that Rob suggested - of all the replies above, that's probably the best suggestion.

Oleg