| Author |
Message
|
| kunal07 |
 Posted: Tue Feb 25, 2014 1:43 am Post subject: Date casing issue |
 |
|
Acolyte
Joined: 05 Dec 2012
Posts: 72
|
Hi Al,
I am facing an issue while casting the character as date.
I getting one string value ex : 110120 and i have to cast it as date so
SET myDate = CAST (inputdate AS DATE FORMAT 'dd-mm-yy');
but if fails so i have convert the string as 20-01-11 and then cast is as date and its working fine.
but it fails with few input string like 141231. I donnt know whats wrong with it as after converting it becomes 31-12-14 and when i tried it to cast it
SET myDate = CAST (inputdate AS DATE FORMAT 'dd-mm-yy');
Its fail. Please provide any suggestion. |
|
| Back to top |
|
 |
| Gralgrathor |
| Posted: Tue Feb 25, 2014 1:49 am Post subject: |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
A DATE type doesn't have a format.
When you CAST(aDateString as DATE FORMAT fmt), fmt contains the format for parsing aDateString. If you input the value '250214', your fmt should be 'ddMMyy'.
_________________
A measure of wheat for a penny, and three measures of barley for a penny; and see thou hurt not the oil and the wine. |
|
| Back to top |
|
|
| kunal07 |
| Posted: Tue Feb 25, 2014 2:51 am Post subject: |
|
|
Acolyte
Joined: 05 Dec 2012
Posts: 72
|
Working like that only..
one very strange thing i noticed that its working fine for all.
but when the day is greater than 28 then its not working..
ex : 141212,141215,141228.... and so on..
but when it becomes 141229,141230,141231.
it stopped working.
any suggestion? |
|
| Back to top |
|
|
| Gralgrathor |
| Posted: Tue Feb 25, 2014 2:56 am Post subject: |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
Yeah: what's the format you use for parsing the string?
_________________
A measure of wheat for a penny, and three measures of barley for a penny; and see thou hurt not the oil and the wine. |
|
| Back to top |
|
|
| kunal07 |
| Posted: Tue Feb 25, 2014 2:57 am Post subject: |
|
|
Acolyte
Joined: 05 Dec 2012
Posts: 72
|
i conver that string to 31-12-14 and then cast it as
SET myDate = CAST (myDate AS DATE FORMAT 'dd-mm-yy'); |
|
| Back to top |
|
|
| Gralgrathor |
| Posted: Tue Feb 25, 2014 3:01 am Post subject: |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
And
1) *WHY* do you convert it to another format before casting to DATE? Why not just read the original string into a DATE?
2) *HOW* do you convert it to another format before casting to DATE?
_________________
A measure of wheat for a penny, and three measures of barley for a penny; and see thou hurt not the oil and the wine. |
|
| Back to top |
|
|
| kunal07 |
| Posted: Tue Feb 25, 2014 3:03 am Post subject: |
|
|
Acolyte
Joined: 05 Dec 2012
Posts: 72
|
i used the substring to make that format..
my requirement is tohave the format like dd-mm-yy' and i am getting the value as 141128
any solution? |
|
| Back to top |
|
|
| Gralgrathor |
| Posted: Tue Feb 25, 2014 3:06 am Post subject: |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
| kunal07 wrote: |
| 'dd-mm-yy' |
And have you read the info center to find out what the proper format for a date in that form is?
_________________
A measure of wheat for a penny, and three measures of barley for a penny; and see thou hurt not the oil and the wine. |
|
| Back to top |
|
|
| kunal07 |
| Posted: Tue Feb 25, 2014 3:08 am Post subject: |
|
|
Acolyte
Joined: 05 Dec 2012
Posts: 72
|
yes i have checked that and tried all formats ..
but i have to change the initial value to dy||'-'||mnth||'-'||yr; and then used the cast but its not working for vause of days > 28 |
|
| Back to top |
|
|
| Gralgrathor |
| Posted: Tue Feb 25, 2014 3:09 am Post subject: |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
| kunal07 wrote: |
i used the substring to make that format..
my requirement is tohave the format like dd-mm-yy' and i am getting the value as 141128
any solution? |
Yes. Why not cast the *original* string directly into a DATE, using the proper format, as per the examples of the info center?
There's no need for all that mucking about with SUBSTRING.
_________________
A measure of wheat for a penny, and three measures of barley for a penny; and see thou hurt not the oil and the wine. |
|
| Back to top |
|
|
| Gralgrathor |
| Posted: Tue Feb 25, 2014 3:10 am Post subject: |
|
|
Master
Joined: 23 Jul 2009
Posts: 297
|
| kunal07 wrote: |
| but i have to change the initial value to dy||'-'||mnth||'-'||yr; |
No, you don't. Just cast the *original* string into a DATE, and once you have that DATE, you can cast it to any format you like.
_________________
A measure of wheat for a penny, and three measures of barley for a penny; and see thou hurt not the oil and the wine. |
|
| Back to top |
|
|
| kunal07 |
| Posted: Tue Feb 25, 2014 3:14 am Post subject: |
|
|
Acolyte
Joined: 05 Dec 2012
Posts: 72
|
i did the same thing as in the info first..
but it didn't work for me.
my input stream is in format Char : 141128
i have used the code :
Set inputlDate = '141128';
SET myDate = CAST (inputlDate AS DATE FORMAT 'YY-MM-DD'); |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Feb 25, 2014 3:40 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Your input stream is SIX chars long.
You Format pattern is EIGHT chars long.
Please explain how you think that the former can be parsed using the pattern described in the second?
The answer you need has already been given to you. There are many other similar requests in this forum that a simple search would have revealed.
The various time and date format patterns are also described in the InfoCentre. Have you looked there to see if 'YY-MM-DD' is actually a correct pattern?
What is the difference between 'MM' and 'mm'? These can sometimes trip up even experienced developers.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| kunal07 |
| Posted: Tue Feb 25, 2014 3:54 am Post subject: |
|
|
Acolyte
Joined: 05 Dec 2012
Posts: 72
|
i have checked the info centre and work accordingly but not able to parse my six char to date.
i have used substring and make it in same format but while casting its not working.
i think i ididnt got the answer yet because the solution provided tll now its not working at all,... |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Feb 25, 2014 4:11 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Your pattern is "YY-MM-DD".
This pattern DOES NOT MATCH your data.
Your data DOES NOT HAVE any "-" in it. |
|
| Back to top |
|
|
|
|
