datatype conversion from string to date

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 2005 Forums

SSIS and Import/Export (2005)

datatype conversion from string to date

Author

Topic

vamsimahi
Starting Member

29 Posts

Posted - 2008-09-30 : 14:45:18

Hi

I am loading data from CSV files to SQL database. I have a column MFD_DATE where the values are in the form of 03SEP2008. I want to convert this into date datatype. I tried using data conversion transformation but its throwing a error. It seems like I need to use Derived column transformation, I tried with few expressions but unable to do it.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 15:39:10

[code]DECLARE @Sample CHAR(9)

SET @Sample = '03SEP2008'

SELECT CAST(@Sample AS DATETIME)[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 00:00:27

use (DT_DBDATE) [@User::yourfield]

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-10-01 : 08:32:59

If you must use this route, here it is:

SUBSTRING([Column 0],3,3) == "JAN" ? "1/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) : SUBSTRING([Column 0],3,3) == "FEB" ? "2/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) : SUBSTRING([Column 0],3,3) == "MAR" ? "3/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) : SUBSTRING([Column 0],3,3) == "APR" ? "4/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) : SUBSTRING([Column 0],3,3) == "MAY" ? "5/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) : SUBSTRING([Column 0],3,3) == "JUN" ? "6/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) : SUBSTRING([Column 0],3,3) == "JUL" ? "7/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) :
SUBSTRING([Column 0],3,3) == "AUG" ? "8/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) : SUBSTRING([Column 0],3,3) == "SEP" ? "9/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) : SUBSTRING([Column 0],3,3) == "OCT" ? "10/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) : SUBSTRING([Column 0],3,3) == "NOV" ? "11/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) : SUBSTRING([Column 0],3,3) == "DEC" ? "12/" + SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],6,4) : "1/1/1111"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources