Converting varchar in to datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting varchar in to datetime

Author

Topic

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-05-29 : 04:35:20

I have two tables, one is a staging table and the other is the main table. The staging table has two columns which contain dates but the datatype is varchar and not datetime and in the main table the two columns are datetime, both table are exactly the same. When i insert all from the stagging table in to the main table i keep gettinig the following error "Conversion failed when converting datetime from character string."

I can't seem to change the stagging table's datatype from varchar into datetime because then I get loads of errors in SSIS .

Truncate table main_table
insert into main_table([patient name], number, age, cancer, consultant, [admission date], [date of operation], DOSA, operation, laparascopic, Notes, [Month])
select
[patient name],
number,
age,
cancer,
consultant,
right([admission date],4) + substring([admission date], 4,2) + left([admission date],2) [admission date],
right([date of operation],4) + substring([date of operation], 4,2) + left([date of operation],2) [date of operation],
DOSA,
operation,
laparascopic,
Notes,
Month
from Staging_table

Please help

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-29 : 04:51:39

May be you have dates in different formats (such as YMD, MDY, DMY in staging table)?

Add WHERE Condition for both columns as follows:
WHERE ISDATE(right([admission date],4) + substring([admission date], 3,2) + left([admission date],2)) = 1
Refer this link once...
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 04:58:21

what are data types of [admission date],[date of operation] etc? why are you using a expression in select to get their values? what the format in which values are actually stored in it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-05-29 : 06:20:11

If admission date and operation dates are in varchar data type then

may be try this?
select right(admission date,4)+substring(admission date, 3,2)+left(admission date,2)

and make sure that the data in those columns are valid like ('01012013')
or form the query as per data in these columns.

Thanks....

M.MURALI kRISHNA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 06:58:48

quote:
Originally posted by mmkrishna1919

If admission date and operation dates are in varchar data type then

may be try this?
select right(admission date,4),substring(admission date, 3,2),left(admission date,2)

and make sure that the data in those columns are valid like ('01012013')

Thanks....

M.MURALI kRISHNA

Always better to use unambiguos iso format

ie 20130101

see earlier posted link by Chandu

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Muj9
Yak Posting Veteran

75 Posts

Posted - 2013-05-29 : 07:28:07

I fixed the problem my sql query was good it was the data from the excel source which had date written worng in the field. the staging table was picking it up because it was varchar but could not convert to main because the date was written like this 20.052.013 i changed the date to 20.05.2013 and it worked fine.

Thanks for your suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources