No, nothing quite that easy. There isn't any implicit conversion because
your current value has no day specified so the database engine can't
determine that you always want it to be the first of the month.

You can try this, but I'm not guaranteeing anything (makes assumption that if
numeric portion of date is > 50 then 19YY else 20YY):

CREATE TABLE #tmpStringDates
(
strDateCol VARCHAR(10) NOT NULL
)

INSERT INTO #tmpStringDates VALUES ('Jan-04')
INSERT INTO #tmpStringDates VALUES ('Dec-08')
INSERT INTO #tmpStringDates VALUES ('Sep-79')
INSERT INTO #tmpStringDates VALUES ('May-98')
INSERT INTO #tmpStringDates VALUES ('Aug-15')

SELECT CONVERT(VARCHAR, CAST(LEFT(strDateCol, CHARINDEX('-', strDateCol)-1) +
' 01 ' + CASE WHEN RIGHT(strDateCol, 2) > 50 THEN '19' ELSE '20' END +
RIGHT(strDateCol, 2) AS DATETIME), 101) AS NewFormat
FROM #tmpStringDates

DROP TABLE #tmpStringDates


John Eisbrener
SQL Database Administrator
Capitol Insurance Companies

-----Original Message-----
From: Jim Hill [mailto:[Email address protected]
Sent: Thursday, August 30, 2007 1:32 PM
To: Eisbrener, John
Subject: RE: dates

Sorry, not the sharpest tool in the shed here...

Is there a way to just run a function to convert the data in current column?
It's nvarchar..

Something like this???

UPDATE [tblphaseonecontrol] set [cost period] =
Convert (LTRIM(RTRIM([cost period])), 'mmm/yy', 'mm/dd/yyyy')

Jim


No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.484 / Virus Database: 269.12.12 - Release Date: 8/29/2007 12:00
AM

MS Sql Server LazyDBA home page