date problem

mohandl · Post by **mohandl** » Fri Feb 22, 2008 1:45 am

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

ray.wurlod · Post by **ray.wurlod** » Fri Feb 22, 2008 2:30 am

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.

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:")

mohandl · Post by **mohandl** » Fri Feb 22, 2008 3:20 am

I am able to split date part ,but How to split the time portion
Is there any function to split the time portion.

Regards,
Mohan

ArndW · Post by **ArndW** » Fri Feb 22, 2008 6:36 am

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)"

ray.wurlod · Post by **ray.wurlod** » Fri Feb 22, 2008 7:39 am

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.

deva · Post by **deva** » Fri Feb 22, 2008 9:54 am

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 ...

Find the date formate in squl server and use the Iconv() and Oconv() functions .

example is Oconv(Iconv(DSLink44.END_DATE[1,10],"D4/"),"D-DMY[,A,]") , go to help and select as per your formate

gsym · Post by **gsym** » Fri Feb 22, 2008 10:44 am

Hi,

To split the time part alone as per your Date format, try Right function

Right("1/28/2008 1:46:55 PM",10) will result - 1:46:55 PM

Thanks & Regards - gsym

paddu · Post by **paddu** » Fri Feb 22, 2008 12:29 pm

Mohandl,

I had similar requirement and my issue was resolved .Please look at thsi
post .

viewtopic.php?t=107975&highlight=

Thanks
Paddu