Convert varchar data to datetime

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?
OB1CanobieAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
OB1CanobieAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
OB1CanobieAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
INSERT INTO
Call_History(
INSTANCE_ID,
Call_Date
)

SELECT
INSTANCE_ID,
CALL_DATE
FROM Stage_Call_History
where ISDATE(CALL_DATE) = 1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.