topic Date format from date field of xml file in QlikView

Date format from date field of xml file

Thu, 23 Apr 2009 02:35:36 GMT

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.

Date format from date field of xml file

Thu, 23 Apr 2009 03:14:46 GMT

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.

Date format from date field of xml file

rwunderlich — Thu, 23 Apr 2009 03:39:01 GMT

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

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

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

Date format from date field of xml file

Thu, 23 Apr 2009 03:54:18 GMT

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

Date format from date field of xml file

sathishkumar_go — Thu, 23 Apr 2009 03:56:53 GMT

Hey use

MONTH

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

and left(fieldname,4) as Year

Date format from date field of xml file

Oleg_Troyansky — Thu, 23 Apr 2009 05:08:01 GMT

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