Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
If the date is stored as 072009 then try something lik this:
date(date#('01' & MONTHYEAR,'DDMMYYYY')) as INVOICE_DATE
What does the original data field look like?
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.
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
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.
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
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.
If the date is stored as 072009 then try something lik this:
date(date#('01' & MONTHYEAR,'DDMMYYYY')) as INVOICE_DATE
Thanks Tom, that code worked.