Hi all,
My source is text file having one column date value is "1/28/2008 1:46:55 PM",this date i have to populate in a SQL server
While loading i am getting error like "1/28/2008 1:46:55 PM is an Invalid date"
Can any one let me know how can i resolve this problem.
Regards,
Mohan
date problem
Moderators: chulett, rschirm, roy
date problem
MOHAN
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Change the format to one acceptable to SQL Server, particularly CCYY-MM-DD HH:MM:SS or CCYY-MM-DD HH:MM:SS.sss
Split the input into date and time. Use Iconv() and Oconv() functions to reorganize both, then concatenate with a space between.
Split the input into date and time. Use Iconv() and Oconv() functions to reorganize both, then concatenate with a space between.
Code: Select all
Oconv(Iconv(Field(InLink.TheString," ",1,1),"DMDY"),"D-YMD[4,2,2]") : " " : Oconv(Iconv(Field(InLink.TheString," ",2,2),"MTHS"),"MTS:")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Use a substring expression, in this case the date and time are separated by a " " (space), so the date would be "FIELD(In.Column," ",1)"
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
But, because there is a space ahead of your AM/PM designator, you need a variation, as I posted in my original answer, which is a complete solution.
Do purchase premium membership. Revenue from premium membership is totally devoted to helping to defray the hosting and bandwidth costs incurred by DSXchange. And you'll be able to read premium posters' contributions in their entirety.
Do purchase premium membership. Revenue from premium membership is totally devoted to helping to defray the hosting and bandwidth costs incurred by DSXchange. And you'll be able to read premium posters' contributions in their entirety.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Find the date formate in squl server and use the Iconv() and Oconv() functions .ray.wurlod wrote:Change the format to one acceptable to SQL Server, particularly CCYY-MM-DD HH:MM:SS or CCYY-MM-DD HH:MM:SS.sss
Split the input into date and time. Use Iconv() and Oconv() functions to reorganize b ...
example is Oconv(Iconv(DSLink44.END_DATE[1,10],"D4/"),"D-DMY[,A,]") , go to help and select as per your formate
Mohandl,
I had similar requirement and my issue was resolved .Please look at thsi
post .
viewtopic.php?t=107975&highlight=
Thanks
Paddu
I had similar requirement and my issue was resolved .Please look at thsi
post .
viewtopic.php?t=107975&highlight=
Thanks
Paddu
There is nothing good or bad, but thinking makes it so. --William Shakespeare