Solved

Help with SQL date conversion

Posted on 2014-10-01
2
194 Views
Last Modified: 2014-10-01
I have a column named birth_date which is varchar(8).  I am trying to convert this to a date and keep getting:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

The data in the column is formatted as 01011997.

I thought this would work, but it does not and I'm not sure why:
convert(date,birth_date,101)

Any help appreciated!
0
Comment
Question by:jasonbrandt3
[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
2 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40355298
cast(right(birth_date, 4) + left(birth_date, 4) as date)
0
 

Author Closing Comment

by:jasonbrandt3
ID: 40355338
Perfect!!!!  Thank you so much!!!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 extract information from SQL Server on Database, Connection and Server properties

752 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