Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett, rschirm, roy
-
Sreenivasulu
- Premium Member
![Premium Member Premium Member](./images/ranks/premium.gif)
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Post
by Sreenivasulu »
Hi All
1.I pass a column containing varchar as 31/12/2010
2.Apply a StringToDate function
3.In the Sqlserver database its stored as 31/12/2011
Any idea how to fix this ?
Regards
Sreeni
-
chulett
- Charter Member
![Charter Member Charter Member](./images/ranks/charter_member%s.gif)
- Posts: 43085
- Joined: Tue Nov 12, 2002 4:34 pm
- Location: Denver, CO
Post
by chulett »
Post your syntax.
-craig
"You can never have too many knives" -- Logan Nine Fingers
-
dsuser_cai
- Premium Member
![Premium Member Premium Member](./images/ranks/premium.gif)
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 pm
Post
by dsuser_cai »
I use a to_char function to convert the date to string if i use a ODBC stage.
Thanks
Karthick
-
Sreenivasulu
- Premium Member
![Premium Member Premium Member](./images/ranks/premium.gif)
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Post
by Sreenivasulu »
HI Craig,
Pls find the code
======
StringToDate(Left(DSLink5.MaturityDate,10) ,"%dd/%mm/%yyyy")
======
karthick: I am not selecting a date value but inserting a date value
Regards
Sreeni
-
dsuser_cai
- Premium Member
![Premium Member Premium Member](./images/ranks/premium.gif)
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 pm
Post
by dsuser_cai »
use single quote:
StringToDate(Left(DSLink5.MaturityDate,10) ,'%dd/%mm/%yyyy')
Thanks
Karthick
-
ArndW
- Participant
- Posts: 16318
- Joined: Tue Nov 16, 2004 9:08 am
- Location: Germany
-
Contact:
Post
by ArndW »
No difference between single and double quotes in these stages. But what value is written to a peek stage or sequential file?
-
Sreenivasulu
- Premium Member
![Premium Member Premium Member](./images/ranks/premium.gif)
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Post
by Sreenivasulu »
Hi ArndW,
Peek stage and sequential stage gives 31/12/2010
Regards
Sreeni
-
ArndW
- Participant
- Posts: 16318
- Joined: Tue Nov 16, 2004 9:08 am
- Location: Germany
-
Contact:
Post
by ArndW »
Do other dates get loaded correctly or is there always 1 year difference?
-
Sreenivasulu
- Premium Member
![Premium Member Premium Member](./images/ranks/premium.gif)
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Post
by Sreenivasulu »
All other dates load correcty only this one has a year difference
Regards
Sreeni
-
Sreenivasulu
- Premium Member
![Premium Member Premium Member](./images/ranks/premium.gif)
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Post
by Sreenivasulu »
Hi Karthick,
The suggestion provided by you did not work. It still gives the same bug
Regards
Sreeni
-
ArndW
- Participant
- Posts: 16318
- Joined: Tue Nov 16, 2004 9:08 am
- Location: Germany
-
Contact:
Post
by ArndW »
And if you manually use SQL to do the same insert statement is the error the same?
-
Sreenivasulu
- Premium Member
![Premium Member Premium Member](./images/ranks/premium.gif)
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Post
by Sreenivasulu »
That is my last hope. But i do not want to do this way since then we need to change all the jobs to user-defined sql
Regards
Sreeni
-
Sainath.Srinivasan
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Post
by Sainath.Srinivasan »
I have used SQL Server in Windows PX Platform. Never had such issues with date.
Try creating a dummy table with 2 columns - varchar and datetime.
Pass same value (after typecast) to both and check the result.
Also run an sql insert via SQL Server front-end rather than DataStage to verify the outcome.
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
-
Contact:
Post
by ray.wurlod »
Is there a trigger or similar database object that might modify the date?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.