posted on Thursday, July 14, 2005 7:02 PM by Knight_Reign

Converting a String Date

Converting a string date in the form YYYYMMDD to a date

This question comes up a lot. How to convert a date in string format to a date type.

You can write a simple derived column expression to parse this out and convert it to a date. You can set the derived column to replace the string date column or create a new one.

Here's the expression to do it:

(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))

That will convert a string date column like this:

Date

Derived Column 1

20050112

1/12/05

20031122

11/22/03

20050509

5/9/05

20010101

1/1/01

20000301

3/1/00

20021003

10/3/02

20022002

2/20/02

19631003

10/3/63

19621002

10/2/62

20051111

11/11/05

Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden

 

Comments

# re: Converting a String Date @ Saturday, July 16, 2005 8:09 AM

Why wouldn't you just cast the string to a datetime?

SELECT CAST('20050112' as DATETIME).

Seems like a lot of effort for no gain. Or am i missing something

Clayton Firth

# re: Converting a String Date @ Saturday, July 16, 2005 8:12 AM

Why wouldn't you just cast the string to a datetime?

SELECT CAST('20050112' as DATETIME).

Seems like a lot of effort for no gain. Or am i missing something?

Clayton Firth

# re: Converting a String Date @ Monday, July 18, 2005 7:28 PM

why not just use this?

print convert(varchar(12),CAST('20050112' as DATETIME),101)

i think this is a better approach than what you are doing...

keith rull

# re: Converting a String Date @ Monday, July 18, 2005 10:15 PM

I think some giving comments may not be aware that the solution is proposed for the expression evaluator in SSIS. The proposed simple solutions are not valid there.

Knight_Reign