FWIW:
select convert(varchar(11),cast('Jan 2007' as datetime),101) returns
'01/01/2007'...at least in SQL 2000 it does...
But as John has said, if you have mmm/yy then it fails. But if you take out
the slash in the middle, leaving mm yy, then convert yy to a 4 digit year
(like John does below), then it works.
WJS
-----Original Message-----
From: Eisbrener John
[mailto:mssqldba-ezmlmshield-x54276271.[Email address protected]
Sent: Thursday, August 30, 2007 11:49 AM
To: LazyDBA Discussion
Subject: RE: dates
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
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page