Hi,
[Payment Date] field data type seems date, shows
Jan 1, 1900
Jan 13, 1900 . . . etc
Added another calculated field in report studio:
year([Payment Date]), shows
1990
1990 . . . etc
Then when I tried to add another field as follows (since I would to convert to the year to text to build another string):
cast(year([Payment Date]),varchar(4))
It gave me an error of "An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'."
Any ideas would be appreciated.
How do you get "year([Payment Date]"?
From Report Studio, drag the "Data Item" from "Insertable Objects" panel.
Double clicked on the Data Item to add "year([Payment Date])"
So the year() is a built in function I believe.
Output as follows:
Payment Date Data Item1
Jan 1, 0001 1
Jan 1, 1900 1900
Jan 2, 1900 1900
Jan 3, 1900 1900
Jan 4, 1900 1900
Jan 5, 1900 1900
I did it so in my rapport
cast(cast(extract([Payment Date];year);integer);varchar(4))
or try it in steps
Try also to_string
I tried your code but did not work . . .
________________________________________________________
Then I checked the syntax for extract and cast are as follows:
extract ( datepart , datetime_exp )
Returns an integer representing the value of datepart (year, month, day, hour, minute, second) in "datetime_exp".
cast ( exp, datatype_specification )
Converts "exp" to a specified data type. Some data types allow for a length and precision to be specified. Make sure that the target is of the appropriate type and size. The following datatypes can be specified: CHARACTER, VARCHAR, CHAR, NUMERIC, DECIMAL, INTEGER, SMALLINT, REAL, FLOAT, DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, and INTERVAL. When type casting to an INTERVAL type, one of the following interval qualifiers must be specified: YEAR, MONTH, or YEAR TO MONTH for the year-to-month interval datatype; DAY, HOUR, MINUTE, SECOND, DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, HOUR TO MINUTE, HOUR TO SECOND, or MINUTE TO SECOND for the day-to-second interval datatype. More notes for this function are included in the user guide
________________________________________________________
So I changed your code to comma
cast(cast(extract([Payment Date],year),integer),varchar(4))
but it still did not work.
I also tried to do one simple code as:
extract([Payment Date],year)
it still did not work, gave me this error:
Parsing error before or near position: 23 of: "extract([Payment Date]"
OK, report studio is wried . . .
if I changed the code to this, it worked . . .
cast (year([Accident Date]),CHAR(4))
Now I am ready to construct the year and month string as follows:
cast (year([Payment Date]),CHAR(4)) + '-' + cast (month([Payment Date]),CHAR(2))
Output shows:
Payment Date Data Item1 Data Item2
Jan 1, 0001 1 1 -1
Jan 1, 1900 1900 1900-1
Jan 2, 1900 1900 1900-1
Jan 3, 1900 1900 1900-1
Question,
If the month is from Jan to Sept, how do I put a zero in the front
For example, Jan 1, 1990 will become 1990-01 (currently showing 1990-1 as above) ?
Thanks.
You have to modulate your month something like
cast(( ((month([Payment Date])/100)+1)*100),CHAR(2))
it returns for month 101 for januari (1/100=0,01 +1=1,01*100=101)
for december it is 112 (12/100=0,12+1=1,12*100=112)
THEN you use substring(month,2,2)= 01 for januari,,,,,12 for december
(I use duth version, thats why it is ; instead of , in syntax)
greatings
Thanks
This will give me
((cast(month([Payment Date]),integer)/100)+1)*100
109 for Sept
110 for Oct
However when I try
cast(((cast(month([Payment Date]),integer)/100)+1)*100,CHAR(2))
it gave me
1. for Sept
1. for Oct
if I use cast(((cast(month([Payment Date]),integer)/100)+1)*100,CHAR(3))
It gave me
1.0 for Sept
1.1 for Oct
. . . I am puzzled
Thanks and I got to take off from work, and thanks so far for your help, it's been very helpful :)
FYI,
If use CHAR(4)
it gave me
1.09 for Sept
1.1E for Oct
O.K
"((cast(month([Payment Date]),integer)/100)+1)*100
109 for Sept
110 for Oct
"
You have an integer now; make it as a string(case blablabla) and use substring to extract 09 en 10
The only hope I could extract integer with leading zero is to turn it into string first, but if I use the cast function, it turned it into engineering notation, that is.
cast(((cast(month([Payment Date]),integer)/100)+1)*100,CHAR(5))
will give
1.09E2 for Sept
1.1E2 for Oct
This is messy as if I use the substr after this:
substr(cast(((cast(month([Payment Date]),integer)/100)+1)*100,CHAR(6)),2,3)
will give
.09 for Sept
.1E for Oct
Are there easier ways . . . I wish it could be as easy as Excel functions as below . . .
Sub test()
StrDate = "2/15/2010"
myDate = CDate(StrDate)
myStr = Year(myDate) & "-" & Format(Month(myDate), "MM")
' This will give 2010-02
End Sub
OR
Sub test2()
myStr = Year(CDate("2/15/2010")) & "-" & Format(Month(CDate("2/15/2010")), "MM")
' This will give 2010-02
End Sub
Quote from: OhMyCognos on 02 Nov 2011 01:10:31 PM
Hi,
[Payment Date] field data type seems date, shows
Jan 1, 1900
Jan 13, 1900 . . . etc
Added another calculated field in report studio:
year([Payment Date]), shows
1990
1990 . . . etc
Then when I tried to add another field as follows (since I would to convert to the year to text to build another string):
cast(year([Payment Date]),varchar(4))
It gave me an error of "An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'."
Any ideas would be appreciated.
A bit of advice : Do not mix native and Cognos sql syntax within one expression, that will lead to problems. Always try to apply 100% Cognos functions first
So what are you trying to see as a result?
very simple, I would like to see a string YYYY-MM based on a [Payment Date] field.
This is the current situation:
If [Payment Date] has a value of 2010-10-1 and 2010-9-30, I would like to get
2010-10 and 2010-09
If I try to use the most proven working functions as below:
cast(extract(year,[Payment Date]),VARCHAR(4))
This would give the year '2010'
If use
cast(extract(month,[Payment Date]),VARCHAR(2))
This would give the month '10' and '9'
If use
repeat('0',2)
This would give '00'
If use
char_length (cast(extract(month,[Payment Date]),VARCHAR(2)))
This would give 2 and 1
If use
cast(extract(year,[Payment Date]),VARCHAR(4)) + '-' + repeat('0',2)
This would give
2010-00
The logic goes if I use Repeat to report the '0' as the leading zero for Sept, then the code should be
cast(extract(year,[Payment Date]),VARCHAR(4)) + '-' + repeat('0',(2-char_length (cast(extract(month,[Payment Date]),VARCHAR(2)))))
But this gave me an error of
An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.
I am really puzzled . . .
cast(extract(year,[date]),varchar(4))||'-'||
(
case when extract(month,[date]) < 10 then
'0'||cast(extract(month,[date]),varchar(1)) else
cast(extract(month,[date]),varchar(2)) end
)
Wow that was it . . . nice, short and sweet !!
Thanks blom0344 :)
To format date data as YYYY-MM-DD, I've also found that the following works:
extract (year,[DATE])||'-'||floor(extract (month,[DATE])/10)||mod(extract (month,[DATE]),10)||'-'||floor(extract (day,[DATE])/10)||mod(extract (day,[DATE]),10)
January 1, 2015 becomes 2015-01-01. November 31, 2015 becomes 2015-11-31. Et cetera. The resulting data still sorts correctly.