asked on

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?

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

OB1Canobie

ASKER

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

Aneesh

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

OB1Canobie

ASKER

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.

Aneesh

INSERT INTO
Call_History(
INSTANCE_ID,
Call_Date
)

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