| Author |
Message
|
| nukalas2010 |
 Posted: Wed Feb 29, 2012 5:50 am Post subject: CASTING DATE format |
 |
|
Master
Joined: 04 Oct 2010
Posts: 220
Location: Somewhere in the World....
|
Hi All,
Here I am facing a problem while casting DATE. I need to CAST the character value "DD-MON-YY" to "YYYY-MM-DD".
ex: "23-APR-08" to "2008-04-23"
Thanks in Advance..  |
|
| Back to top |
|
 |
| mqsiuser |
| Posted: Wed Feb 29, 2012 5:53 am Post subject: Re: CASTING DATE format |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
not tested, but should be very similiar to this:
| Code: |
| SET myDateString2 = CAST( CAST( myDateString AS DATE FORMAT 'DD-MON-YY') AS CHAR FORMAT 'YYYY-MM-DD' ); |
And: You should really read the Infocenter !
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| nukalas2010 |
| Posted: Thu Mar 01, 2012 2:52 am Post subject: Re: CASTING DATE format |
|
|
Master
Joined: 04 Oct 2010
Posts: 220
Location: Somewhere in the World....
|
| mqsiuser wrote: |
not tested, but should be very similiar to this:
| Code: |
| SET myDateString2 = CAST( CAST( myDateString AS DATE FORMAT 'DD-MON-YY') AS CHAR FORMAT 'YYYY-MM-DD' ); |
! |
Hi Thanks for yr reply.. Bt its very simple 
SET target = CAST(cdate AS DATE FORMAT 'dd-MMM-yy');
And really, the infocenter is not clear about this..  |
|
| Back to top |
|
|
| adubya |
| Posted: Thu Mar 01, 2012 2:58 am Post subject: Re: CASTING DATE format |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
| nukalas2010 wrote: |
And really, the infocenter is not clear about this.. |
The infocenter link you were given has lots of examples and a link to formatting and parsing dateTimes as String page which has a table showing all the character values used in formatting strings. If that's unclear then I'm a Dutchman  |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Thu Mar 01, 2012 3:11 am Post subject: Re: CASTING DATE format |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| nukalas2010 wrote: |
Hi Thanks for yr reply.. Bt its very simple
SET target = CAST(cdate AS DATE FORMAT 'dd-MMM-yy'); |
"target" is/must have been declared (by you) as DATE.
Consider this: There will be an implicit (for you that means uncontrolled) cast to CHAR if you concatenate "target" with some CHAR (with "||"), which then does the "default-formatting"... which is what you want.
You are just lucky
You probably do not want to live with this, since you don't want to leave things to be not explicit and clear... that can/might come back later: There are a couple of format-options (as you can see in the infocenter)... carefully think about which is the right one for you.
And you are right, the infocenter is not clear about the "default (string) formatting" of DATE ... but probably you should just not do that. ESQL usually is strict/explicit, there is no / there should not be wishy washy in you interfaces. Thats why this information is missing in the infocenter: You should not rely on this information (probably it is "yyyy-MM-dd" or probably "yyyy-MM-DD" ).
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| kimbert |
| Posted: Thu Mar 01, 2012 4:32 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
|
| Back to top |
|
|
| mqsiuser |
| Posted: Thu Mar 01, 2012 4:39 am Post subject: |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
Great Kimbert, thank you for clarifying. That's ingenious, the type followed by a space and then 'yyyy-mm-dd'.
@OP: That makes it explicit on any trace and (almost) impossible to not put it (from DATE) in(to CHAR) as an explicit cast (in your code).
So: Do as I suggested in my post
Also: You must have overlooked the string/CHAR "DATE " in your output ?!
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
|
|
