Solved

Convert varchar Mon-yyyy  to date

Posted on 2012-04-02
8
551 Views
Last Modified: 2012-04-03
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
Comment
Question by:CarenC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 37796697
DECLARE @V VARCHAR(8) = 'APR-2012'

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

Author Comment

by:CarenC
ID: 37796759
I don't know how or where to use the DECLARE statement.  All I know to do is run a SELECT.
0
 
LVL 18

Accepted Solution

by:
lludden earned 500 total points
ID: 37796871
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 6

Expert Comment

by:rkannan2000
ID: 37797307
Hi lludden,
I think the following might help.

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

Regards
Kannan
0
 

Author Closing Comment

by:CarenC
ID: 37801457
lludden's solution worked.  rkannan2000's didn't work . . . the error was str_to_date is not a valide function.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37801468
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
 

Author Comment

by:CarenC
ID: 37801490
Scott . . . good suggestion but can't make that change in this situation.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37801579
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
encrypt SQL Server 2008 port 1433 3 42
SQL Query (lookup) 8 57
Replacing unrecognized function name in SQL Query 4 44
database audit for object access 6 38
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question