Date Formatting

[Guest_imported]

Hi,

Could anyone help me with this?

In Access, this works fine...
SELECT Format(create_date, 'mm/dd/yyyy')
FROM db2inst1_proj;

But in db2, the above statement does not work.

Thanks,

Naveen

 

[MatMan]

I guess that there's not FORMAT function in DB2.

You're actually running the statement from Access with a linked (I'm guessing here) table to the DB2 database.

 

[esskayind]

Hi,
In DB2,Format function is not available.By default the date format is mm/dd/yyyy

If you want to have different format,you can use the following

LTIRM(RTRIM(CHAR(DAY(l_date))))||LTIRM(RTRIM(CHAR(MONTH(l_date))))||LTRIM(RTRIM(CHAR(YEAR(l_date)))

The above one is just a example.If you want month name ,then you can use MONTHNAME() function in place of MONTH().

Regards,
Thangam

 

[Dhafir]

Hi,

I have tried the alternative described by esskayind as:
LTIRM(RTRIM(CHAR(DAY(l_date))))||LTIRM(RTRIM(CHAR(MONTH(l_date))))||LTRIM(RTRIM(CHAR(YEAR(l_date)))

but got an error says:
No function by the name "LTIRM" having compatible arguments was found in the function path !

any idea ?

Thanks
Dhafir

 

[MarcLodge]

Dahfir,
I think this is a typo and should be LTRIM
Marc

 

[Dhafir]

Yes Marc it is a typo , I realized that later after I posted this. Probably I was distracted by the too many brackets.

Thanks

 

[supernovae]

btw, when u use the above statement , the result wuld be
2002421 , the '0' in '04' is truncated becoz of the char(), is there any way to retain the month as MM i.e '04' and not '4' .
same applies for the char(day()) function.

 

[leveyp]

Something like this will do the trick

select
substr(char(year(current date)),1,4)||
case when month(current date) <= 9 then
'0'||substr(char(month(current date)),1,1)
else substr(char(month(current date)),1,2) end
||
case when day(current date) <= 9 then
'0'||substr(char(day(current date)),1,1)
else substr(char(day(current date)),1,2) end
from yourtable