String to date 3

[jadams0173]

Using SQL 2000

I have a string that represents a date in our MRP system. I need to take this string to a valid date format. The string is MMDDYY (081309) and I would like to convert it to yyyy-mm-dd.

Thanks in advance.

 

[hmckillop]

I think there has got to be a simplier way, but anyway this works

declare @v_string varchar(6)

select @v_string = '081309'
select CAST(substring(Right('000000' + convert(varchar(6),@v_string),6),1,2)+'/'+substring(Right('000000' + convert(varchar(6),@v_string),6),3,2)+'/'+substring(Right('000000' + convert(varchar(6),@v_string),6),5
,2) as datetime) as variablename

"I'm living so far beyond my income that we may almost be said to be living apart

 

[jbenson001]

If you know you will always have 6 characters in the field:

declare @dString as varchar(10)

set @dString = '081309'
set @dString = LEFT(@dString, 2) + '/' + SUBSTRING ( @dString ,3 , 2 ) + '/' + Right(@dString ,2)

Select cast(@dString as datetime)

 

[bborissov]

If you want NOT to depends of SQL Server SET DATEFORMAT use so called ISO format: YYYYMMDD
(based on jbenson001 example)

declare @dString as varchar(10)
set @dString = '081309'
set @dString = '20'+RIGHT(@dString, 2) + LEFT(@dString, 4)
Select cast(@dString as datetime)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jadams0173]

Thanks to all! I ended up using bborissov version.