convert numbervalue MMYYYY

[karensmith]

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?

[#2]

What does the original data field look like?

[karensmith]

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.

[prieper]

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

[karensmith]

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.

[#6 · Accepted solution]

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

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

[karensmith]

Thanks Tom, that code worked.