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.
| 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:00AMExpected output 20/01/1997 12:00AMthanks 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 applicationdeclare @date varchar(100)='Jan20199712:00AM'select convert(datetime,stuff(stuff(stuff(@date,6,0,' '),4,0,' '),12,0,' ') ,109)MadhivananFailing to plan is Planning to fail |
 |
|
|
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:00AMExpected output 8/01/1997 12:00AMthanks and regards,Vipin jha |
|
|
|
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) |
|
|
|
|
|
|
|
|
Date format issue