Convert Date to String

[OhMyCognos]

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.

[johangel]

How do you get "year([Payment Date]"?

[OhMyCognos]

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

[johangel]

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

[OhMyCognos]

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]"

[OhMyCognos]

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.

[johangel]

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

[OhMyCognos]

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 :)

[OhMyCognos]

FYI,

If use CHAR(4)

it gave me

1.09 for Sept
1.1E for Oct

[johangel]

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

[OhMyCognos]

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

[blom0344]

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

[RL_Boston]

So what are you trying to see as a result?

[OhMyCognos]

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 . . .

[blom0344]

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
)

[OhMyCognos]

Wow that was it . . . nice, short and sweet !!
Thanks blom0344 :)

[marvin.k9]

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.