Got a char need to convert to Date

[sanjdhiman]

Hi there I have a field, that is set up as follows

CallStartDate char(6)

e.g. 011204

1st Dec 2004

I need to copy this field for each row across to another table but convert it into a date field, i.e. 01-12-04 but i have tried to do it using the DTS transformation package but no joy.. any ideas on how to convert this char to a date value in the format dd-mm-yy (Note my regional settings are set to dd-mm-yy already)

Any help would be great

Thank u in advance

Sanj

 

[BuGlen]

Hi Sanj,

You can use the Cast() function to accomplish what you want, but you will need to rearrange the position of the date values in the string because it expects the YYMMDD format in a 6 digit expression. Here's an example of how you might use it:

declare @DateString varchar(6)
set @DateString = '011204'

select cast(
	substring(@DateString, 5, 2)
	+ substring(@DateString, 1, 2)
	+ substring(@DateString, 3, 2) 
as datetime) as DateField

If you run this in Query Analyzer, you will see that the cast function correctly converts the character value to a date.

Hope this helps.

- Glen

Know thy data.