asked on

Convert varchar Mon-yyyy to date

My date field is DATE (varchar(8)) and is in the format Mon-YYYY (ex: Apr-2010). How do I convert to a date using the 1st of the month. I tried several solutions I found here and on the web but couldn't get any to work.

lludden

DECLARE @V VARCHAR(8) = 'APR-2012'

SELECT cast('01-' + @v AS DATETIME)

CarenC

ASKER

I don't know how or where to use the DECLARE statement. All I know to do is run a SELECT.

ASKER CERTIFIED SOLUTION

lludden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rkannan2000

Hi lludden,
I think the following might help.

SELECT STR_TO_DATE(concat('01-',myDate) , '%d-%M-%Y') FROM myTable

Regards
Kannan

CarenC

ASKER

lludden's solution worked. rkannan2000's didn't work . . . the error was str_to_date is not a valide function.

Scott Pletcher

YYYYMMDD is a 100% safe format *and* will fit in the existing space if you want to permanently change the column's data.

SELECT
RIGHT(date, 4) + RIGHT('0' + CAST((CHARINDEX(LEFT(date, 3),
'JanFebMarAprMayJunJulAugSepOctNovDec') + 2) / 3 AS varchar(2)), 2) + '01' AS date
FROM dbo.tablename
WHERE
...

UPDATE dbo.tablename
SET date = RIGHT(date, 4) + RIGHT('0' + CAST((CHARINDEX(LEFT(date, 3),
'JanFebMarAprMayJunJulAugSepOctNovDec') + 2) / 3 AS varchar(2)), 2) + '01'

CarenC

ASKER

Scott . . . good suggestion but can't make that change in this situation.

Scott Pletcher

Would still work as a SELECT, of course :-) :

SELECT
CAST(RIGHT(date, 4) + RIGHT('0' + CAST((CHARINDEX(LEFT(date, 3),
'JanFebMarAprMayJunJulAugSepOctNovDec') + 2) / 3 AS varchar(2)), 2) + '01' AS date) AS date