Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
how to insert date from one table to other

Answered how to insert date from one table to other

  • Sunday, March 17, 2013 5:44 AM
     
     
    Sign In to Vote
    0

    i have two tables datatrans and temp

    i want to insert temp.date into datatrans.date

    my problem is temp.date (varchar) and datatrans.date in (datetime)

     

All Replies

  • Sunday, March 17, 2013 6:36 AM
     
      Has Code
    Sign In to Vote
    0
    what's the format of temp.date?? if it is dd\mm\yyyy, you can below... 
    set dateFormat dmy

declare @a table (sno varchar(10))
insert into @a values ('31/12/2011')
declare @b table (sno datetime)
insert into @b
select * from @a
select * from @b

    Hope it Helps!!

     
  • Sunday, March 17, 2013 6:45 AM
     
     
    Sign In to Vote
    0

    25/02/2013

    It read from text file

     
  • Sunday, March 17, 2013 7:26 AM
     
      Has Code
    Sign In to Vote
    0

    25/02/2013

    It read from text file

    try the code above or the one below..they both should work

    declare @a table (sno varchar(10))

insert into @a values ('31/12/2011')

declare @b table (sno datetime)

insert into @b
select CONVERT(datetime,sno,103) from @a

select * from @b

    Hope it Helps!!

     
  • Sunday, March 17, 2013 7:34 AM
     
     
    Sign In to Vote
    0

    IT IS NOT ONLY A SINGLE DATE

    i want to update one table column to another table column

    temp.date (varchar) and datatrans.date in (datetime)
     
  • Sunday, March 17, 2013 7:36 AM
    Moderator
     
     
    Sign In to Vote
    0

    When you are dealing with date literals, the best to use ANSI date: YYYY-MM-DD .

    UPDATE statement examples including updating with JOIN:

    http://www.sqlusa.com/articles2005/sqlupdate/

    Datetime string conversion:

    http://www.sqlusa.com/bestpractices/datetimeconversion/

    Kalman Toth Database & OLAP Architect sqlusa.com
    Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012



     
  • Sunday, March 17, 2013 7:55 AM
     
      Has Code
    Sign In to Vote
    0

    IT IS NOT ONLY A SINGLE DATE

    i want to update one table column to another table column

    temp.date (varchar) and datatrans.date in (datetime)

    declare @a table (sno int,sname varchar(10))
insert into @a values (1,'31/12/2011'),(2,'31/12/2012')
declare @b table (sno int,sname datetime)

insert into @b(sno) values (1),(2)

update @b set sname= CONVERT(datetime,A.sname,103)
from @a A INNER JOIN @b B on A.sno=B.sno

/*set dateformat dmy
update @b set sname= A.sname
from @a A INNER JOIN @b B on A.sno=B.sno
*/
select * from @b

    Hope it Helps!!

     
  • Sunday, March 17, 2013 8:32 AM
     
     
    Sign In to Vote
    0

    @stan210

    sorry i can't follow this

    please show this query into my table name.........

    at first my table column  "temp.Cdate" have set of date and its data type is (varchar (50))

    25/08/2011
    23/08/2011
    15/08/2011
    25/08/2012
    25/08/2011
    25/08/2011
    23/08/2012
    25/02/2011
    25/08/2011
    12/08/2013

    25/02/2013

    i want to insert this date into another table DATATRANS , column name is cdate , it is in datetime

     
  • Sunday, March 17, 2013 8:36 AM
     
     Answered Has Code
    Sign In to Vote
    0

    you just need to replace the table names .... if you are not sure, please try it on test system...

    However, you should have JOIN column on both the tables, what is it??? In the example above, sno is my JOIN column, you should something like that to update values on one table with other tables values...

    update DataTrans set cdate= CONVERT(datetime,A.Cdate,103)
from temp A INNER JOIN DataTrans B on A.<<YOURJOINCOLUMN>>=B.<<YOURJOINCOLUMN>>

OR
set dateformat dmy

update DataTrans set cdate= A.Cdate
from temp A INNER JOIN DataTrans B on A.<<YOURJOINCOLUMN>>=B.<<YOURJOINCOLUMN>>

    Hope it Helps!!



     
  • Sunday, March 17, 2013 8:59 AM
     
     
    Sign In to Vote
    0

     thank.....

    now my tube is blinking

    what is  A , B and 103

     
  • Sunday, March 17, 2013 9:49 AM
     
     
    Sign In to Vote
    0
    A is alias for table 'Temp', B Is alias for table "DataTrans"..103 is the format code to tell the dateformat is dd/mm/yyyy ..refer this http://www.sql-server-helper.com/tips/date-formats.aspx

    Hope it Helps!!

     
  • Tuesday, March 19, 2013 1:20 AM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    Hi midhous,

    We can use Convert function to convert varchar to datetime, please refer to the following codes:

    create table temp
(
	ID int,
	Cdate varchar(50)
)

insert into temp values (1,'25/08/2011');
insert into temp values (2,'23/08/2011');

create table DATATRANS
(
	ID int,
	cdate datetime
)

insert into DATATRANS(ID) values (1);
insert into DATATRANS(ID) values (2);

update DATATRANS set cdate=CONVERT(datetime,a.Cdate,103)
from temp A INNER JOIN DataTrans B on A.ID= b.ID

select * from temp;
select * from DATATRANS

drop table temp;
drop table DATATRANS;

    Allen Li
    TechNet Community Support

     
  • Tuesday, March 19, 2013 4:05 AM
     
     Proposed Answer Has Code
    Sign In to Vote
    0
     
    --IF TABLES ARE LIKE BELLOWS
create table temp
(
	ID int,
	Cdate varchar(50)
)

insert into temp values (1,'25/08/2011');
insert into temp values (2,'23/08/2011');

create table DATATRANS
(
	ID int,
	cdate datetime
)

-- THEN

-- If you need insert to another table
Insert Into DATATRANS(id,cdate)
select id, cast(Cdate  as datetime) from temp

-- If you need update another table by a relation
update dt
set dt.cdate = cast(tmp.Cdate  as datetime) 
from DATATRANS dt inner join temp tmp
	on dt.id = tmp.id