CODEWITHSUNDEEP
sql-server
shakeel ahmad
shakeel ahmad

Reputation: 149

How to get date from one table and insert into another table

I used following queries to get date from one table and insert into another table.

INSERT INTO InstallmentPaymentHistory 
VALUES('DateSold')

SELECT CONVERT(VARCHAR(11), s.DateSold, 113) AS DateSold  
FROM SalesInvoice s 
WHERE s.SalesInvoiceID = 9;

but I get an error

Conversion failed when converting date and/or time from character string

I have also tried

INSERT INTO InstallmentPaymentHistory 
VALUES('YYYY.MM.DD')  

SELECT CONVERT(VARCHAR(11), s.DateSold, 113) AS DateSold  
FROM SalesInvoice s 
WHERE s.SalesInvoiceID = 9;

But I get the same error.

Table #1

SalesInvoiceID  CustomerID   SoldDate       TotalBill
-------------------------------------------------------     
1                3840        2018.03.22      20000
2                4581        2018.04.11      80000
3                4420        2018.05.13      60000

Table # 2

InstallmentPaymentID    SalesInvoiceID  InstallmentNo     DueDate PaymentDdate
---------------------------------------------------------------                 
1                     2                 1            2018.03.22  2018.3.22
2                     2                 2            2018.04.11  2018.3.22
3                     2                 3            2018.05.13  2018.3.22

Upvotes: 3

Views: 4501

Answers (1)

marc_s
marc_s

Reputation: 754488

Not entirely clear what you're trying to do with those commands......

If you want to use the SELECT, combined with some fixed values, then you should use something like this:

INSERT INTO dbo.InstallmentPaymentHistory (specify-the-list-of-columns-here!)
    SELECT 
        -- provide the fixed values here, first
        11, 9, 2, 'DateSold', '20180306', 6000,
        -- then the "calculated/converted" value from your other table
        CONVERT(VARCHAR(11), s.DateSold, 113) 
    FROM 
        dbo.SalesInvoice s 
    WHERE 
        s.SalesInvoiceID = 9;

Also: it's not clear from your post whether the date string 2018.3.6 refers to the 3rd of June or the 6th of March - but I'd recommend to always use the adapted ISO-8601 format, which is YYYYMMDD (without any dashes or anything!).

So for 6th of March, use 20180306, and for 3rd of June use 20180603

Upvotes: 4

Related Questions