Hi Folks,

I have following query:

select datum, extract(year from datum) "Y",
extract(month from datum) "M",
cast(extract(year from datum) as varchar)+'-'+
cast(extract(month from datum) as varchar) "Period"
from upal

the datum field is a DATE field.

at the moment this shows me this result:

2014-06-16  2014  6   2
2014-06-18  2014  6   2
2014-09-01  2014  9   2

but I would expect

2014-06-16  2014  6   2014-6
2014-06-18  2014  6   2014-6
2014-09-01  2014  9   2014-9

I also tried to cast extract as integer and cast it as varchar like
this:

select datum, extract(year from datum) "Y",
extract(month from datum) "M",
cast(cast(extract(year from datum) as integer) as varchar)+'-'+
cast(cast(extract(month from datum) as integer) as varchar) "Period"
from upal

but it shows same wrong result.

I'm missing anything here but cannot find it, any tips?

Yusuf


I think what's happening is that using VARCHAR without a length means that the sql compiler has to take a guess and in your case its making the wrong guess (probably from the length of the '-'). Try this

select datum, extract(year from datum) "Y",
extract(month from datum) "M",
cast(extract(year from datum) as varchar(4))+'-'+
cast(extract(month from datum) as varchar(2)) "Period"
from upal


Roy Lambert

Roy Lambert wrote:

> select datum, extract(year from datum) "Y",
> extract(month from datum) "M",
> cast(extract(year from datum) as varchar(4))+'-'+
> cast(extract(month from datum) as varchar(2)) "Period"
> from upal

Hi Roy,

yes you are right, that was the problem. I meant that the cast uses
default length of that field ... now working.