• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

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.
0
CarenC
Asked:
CarenC
  • 3
  • 2
  • 2
  • +1
1 Solution
 
lluddenCommented:
DECLARE @V VARCHAR(8) = 'APR-2012'

SELECT cast('01-' + @v AS DATETIME)
0
 
CarenCAuthor Commented:
I don't know how or where to use the DECLARE statement.  All I know to do is run a SELECT.
0
 
lluddenCommented:
I was just doing that to show the syntax.  If the field in your table is MyDate then use

SELECT ELECT cast('01-' + MyDate AS DATETIME) AS MyDate FROM myTable
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
rkannan2000Commented:
Hi lludden,
I think the following might help.

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

Regards
Kannan
0
 
CarenCAuthor Commented:
lludden's solution worked.  rkannan2000's didn't work . . . the error was str_to_date is not a valide function.
0
 
Scott PletcherSenior DBACommented:
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'
0
 
CarenCAuthor Commented:
Scott . . . good suggestion but can't make that change in this situation.
0
 
Scott PletcherSenior DBACommented:
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
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now