topic convert numbervalue MMYYYY in QlikView

convert numbervalue MMYYYY

karensmith — Sat, 11 Jul 2009 00:56:42 GMT

The below code is not converting MMYYYY value correctly it converts it to 11/1/4752
4/1/4780
9/1/4807
1/1/4835
6/1/4862
10/1/4889

monthstart(date(01 & MONTHYEAR,'DDMMYYYY')) as INVOICE_DATE
can someone tell me what I am doing wrong?

convert numbervalue MMYYYY

Sat, 11 Jul 2009 01:09:39 GMT

What does the original data field look like?

convert numbervalue MMYYYY

karensmith — Sat, 11 Jul 2009 02:15:54 GMT

The original data is number field on SQL server ---> to qvd file. I need this value represented by a date filed because other dates in the application are in true date formats.

convert numbervalue MMYYYY

prieper — Sat, 11 Jul 2009 02:34:05 GMT

Hi,

why don't you use the Monthstart-function as per Help-File. Normally QV should be able to "understand" the date from SQL-query - but you may also force it with date()-function first

quote

MonthStart( date [, shift = 0] )

Returns a value corresponding to a timestamp with the first millisecond of the first date of the month containing date. The default output format will be the DateFormat set in the script. Shift is an integer, where the value 0 indicates the month which contains date. Negative values in shift indicate preceding months and positive values indicate succeeding months.

Example:

monthstart ( '2001-10-19' ) returns '2001-10-01' with an underlying numeric value corresponding to '2001-10-01 00:00:00.000'

unquote

HTH

Peter

convert numbervalue MMYYYY

karensmith — Sat, 11 Jul 2009 08:33:43 GMT

this not a true data in sql. Its just a number field that I want to convert to a date MMDDYY. I thought that was what monthstart does, but year is now working correctly.

convert numbervalue MMYYYY

Sat, 11 Jul 2009 20:46:06 GMT

If the date is stored as 072009 then try something lik this:

date(date#('01' & MONTHYEAR,'DDMMYYYY')) as INVOICE_DATE

convert numbervalue MMYYYY

karensmith — Sun, 12 Jul 2009 04:13:21 GMT

Thanks Tom, that code worked.