Hi,

I've two columns in table importDate(char) and Import_Date
(Datetime). I want to import the data of char field in to
datetime field. when I'm trying to insert tha data is
getting messed up.

Please let me know if I've to use any function to convert
Char field to Datetime.

Regards,

Hi Vishal ,

Thanks for replying!
I've a constant format for column Importdate and its in date format only
(YYYY-MM-DD) and both ImportDate(Char) and Import_Date(datetime) are in
same table.
Basically I'm trying to fetch the data in same table from Char column to
DAtetime column.

When i'm trying to insert the data its messing up....

Regards,
Munish



*** Sent via Developersdex http://www.developersdex.com ***

Munish,

Are you having constant format for the "importDate" column's date values if yes then you can make
use of CONVERT function to do the job. see following example.

create table t(exportdt varchar(15),
importDate datetime)

insert into t values ('31-01-2003', null)
insert into t values ('31-12-2003', null)
insert into t values ('31-01-2074', null)

update t set importdate = exportdt
--above statement will give you an error.
--use convert to convert these values to valid datetime.

update t set importdate = convert(datetime,exportdt,105)
--above statement will be successful

--
- Vishal

It should work, What statement are you firing. Since you are updating values from same table as i've
shown you you will have to do an update. Also yyyy-mm-dd is the default date format accepted by sql
so you can do a direct update as follows.

update t set importdate = exportdt

but if any of the row value doesn't have yyyy-mm-dd format then whole batch will get aborted.
--see following example

create table t(exportdt varchar(15),
importDate datetime)
go
insert into t values ('2003-01-31', null)
insert into t values ('2003-12-31', null)
insert into t values ('2074-01-31', null)
insert into t values ('31-01-2003', null) --will fail
insert into t values ('31-12-2003', null) --will fail
insert into t values ('31-01-2074', null) --will fail

Hence in that case you can try to update as many values as you can using following statement.

update t set importdate = exportdt
where isdate(exportdt) = 1 --look for more help on isdate in books online.


--
- Vishal