Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Date format issue

Author  Topic 

vipin_jha123
Starting Member

31 Posts

Posted - 2014-07-22 : 00:33:30
Hi All,

I am facing one serious issue with my flat file source data.
there is one column in flat file called descarge_date which come in below format.
Jan20199712:00AMand i am looking to convert it into DD/M/YYYY WITH Timestamp.
Jan20199712:00AM = Jan 20 1997 12:00AM
Expected output 20/01/1997 12:00AM
thanks in advance,

vipin jha

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-07-22 : 08:03:35
If the flat file has formatted dates, convert it to DATETIME datatype and store it in the column using DATETIME datatype. You can do the formation at the front end application


declare @date varchar(100)='Jan20199712:00AM'
select convert(datetime,stuff(stuff(stuff(@date,6,0,' '),4,0,' '),12,0,' ') ,109)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

vipin_jha123
Starting Member

31 Posts

Posted - 2014-07-23 : 00:05:07
Hi Madhivanan,

Thanks for your post.

but one issue i found if date is less than 10 then I am not gaiting the data as I wanted.

Jan8199712:00AM = Jan 8 1997 12:00AM

Expected output 8/01/1997 12:00AM

thanks and regards,
Vipin jha
Go to Top of Page

tm
Posting Yak Master

160 Posts

Posted - 2014-07-24 : 13:30:24
Based on madhivanan solution .. Try ..

declare @date varchar(100)='Jan2199712:00AM'
select convert(datetime,stuff(stuff(stuff(@date,case when len(@date) = 15 then 5 else 6 end,0,' '),4,0,' '),case when len(@date) = 15 then 11 else 12 end,0,' ') ,109)

Go to Top of Page
   

- Advertisement -