?
Solved

Convert varchar data to datetime

Posted on 2009-12-24
5
Medium Priority
?
419 Views
Last Modified: 2012-05-08
I have two tables in MS SQL Server.  The source field is a varchar data type and has data that I need to move to another table field of DateTime data type.  I need to convert the varchar to a DateTime format.  The data in the source file is in the format yyyymmdd. How can I get the data to append to the new table without getting data type errors?
0
Comment
Question by:OB1Canobie
  • 3
  • 2
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 26119787
you dont really need any sort of explicit conversion if your date is in YYYYmmdd format, sql will automatically convert it to the curresponding date
0
 

Author Comment

by:OB1Canobie
ID: 26119794
I thought so too, but I'm getting an error "Server: Msg 241, Level 16, State 1, Line 4
Syntax error converting datetime from character string." I've attached my sql syntax.
USE
Dialer

INSERT INTO
Call_History(
INSTANCE_ID,
Call_Date
)

SELECT
INSTANCE_ID,
CALL_DATE


FROM
Stage_Call_History

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26119837
are you sure that  "Stage_Call_History" contains the date only in that format ?
run this to find  invalid dates

SELECT
INSTANCE_ID,
CALL_DATE
FROM Stage_Call_History
where ISDATE(CALL_DATE) = 0
0
 

Author Comment

by:OB1Canobie
ID: 26119854
Ok, that returned a row with a non-date format, anyway that we can get rid of that line?  I wil need to run this routine every night and will not have time to review the data to ensure data is correct evertime.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26119945
INSERT INTO
Call_History(
INSTANCE_ID,
Call_Date
)

SELECT
INSTANCE_ID,
CALL_DATE
FROM Stage_Call_History
where ISDATE(CALL_DATE) = 1
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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