If it is a text column and those are truly the values, maybe this routine
that I just threw together will get you what you want. It makes a few
assumptions. First any Numeric value greater than 50 will fall in the 20th
century (i.e. 1996) and any numeric value less than 51 will fall in the 21st
century (i.e. 2007). The second assumption is that the month values in the
column match the month values in the case statement. In the query most
months except September are three letters so I made it tolerable to multiple
length month values. Basically this may need to be fine tuned to fit your
needs if it is in fact a column populated with strings representing the date.
Here's the query:
CREATE TABLE #tmpStringDates
(
strDateCol VARCHAR(10) NOT NULL
)
INSERT INTO #tmpStringDates VALUES ('Jan-04')
INSERT INTO #tmpStringDates VALUES ('Dec-08')
INSERT INTO #tmpStringDates VALUES ('Sept-79')
INSERT INTO #tmpStringDates VALUES ('May-98')
INSERT INTO #tmpStringDates VALUES ('Aug-15')
SELECT
strDateCol,
CASE
WHEN CAST(RIGHT(strDateCol, 2) AS INT) > 50 THEN
CASE LEFT(strDateCol, CHARINDEX('-', strDateCol)-1)
WHEN 'Jan' THEN CAST('1/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Feb' THEN CAST('2/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Mar' THEN CAST('3/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Apr' THEN CAST('4/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'May' THEN CAST('5/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Jun' THEN CAST('6/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Jul' THEN CAST('7/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Aug' THEN CAST('8/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Sept' THEN CAST('9/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Oct' THEN CAST('10/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Nov' THEN CAST('11/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Dec' THEN CAST('12/1/19' +
RIGHT(strDatecol, 2) AS DATETIME)
ELSE NULL
END
ELSE
CASE LEFT(strDateCol, CHARINDEX('-', strDateCol)-1)
WHEN 'Jan' THEN CAST('1/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Feb' THEN CAST('2/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Mar' THEN CAST('3/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Apr' THEN CAST('4/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'May' THEN CAST('5/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Jun' THEN CAST('6/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Jul' THEN CAST('7/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Aug' THEN CAST('8/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Sept' THEN CAST('9/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Oct' THEN CAST('10/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Nov' THEN CAST('11/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
WHEN 'Dec' THEN CAST('12/1/20' +
RIGHT(strDatecol, 2) AS DATETIME)
ELSE NULL
END
END AS ConvertedDateCol
FROM #tmpStringDates
DROP TABLE #tmpStringDates
HTH,
John Eisbrener
SQL Database Administrator
Capitol Insurance Companies
-----Original Message-----
From: Norkett Margaret
[mailto:mssqldba-ezmlmshield-x53971842.[Email address protected]
Sent: Thursday, August 30, 2007 12:52 PM
To: LazyDBA Discussion
Subject: RE: dates
What is the datatype on that column? If it's a string, then you're probably
not going to be able to convert it, however, if it is truly a date/time
field, then what you're seeing is however the application is presenting it
as SQL stores datetimes in a robust format that can be converted to whatever
final format you want to see it in.
For mm/dd/yyyy, use convert(varchar(10), yourdatefield, 101)
BOL has a nice chart of conversions from US to European :)
If you have any questions or concerns, please let me know them.
Margaret Norkett
Database Administrator
Good communication is as stimulating as black coffee and
just as hard to sleep after.
Anne Morrow Lindbergh, 'Gift From the Sea'
~
-----Original Message-----
From: Jim Hill
[mailto:mssqldba-ezmlmshield-x76494370.[Email address protected]
Sent: Thursday, August 30, 2007 1:38 PM
To: LazyDBA Discussion
Subject: dates
Anyone have an easy way to convert a column in a table from date format
mmm-dd to mm/dd/yyyy
Jan-04 to 1/1/2004
Thank you
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
---------------------------------------------------------------------
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