Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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